package com.emm.yixun.website.controller.customer;

import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.emm.yixun.common.app.AddSignedReqDto;
import com.emm.yixun.common.app.AddSubscribeReqDto;
import com.emm.yixun.common.app.servletentity.CustomerFamilyListReq;
import com.emm.yixun.common.model.*;
import com.emm.yixun.common.model.dto.*;
import com.emm.yixun.common.model.enums.*;
import com.emm.yixun.customer.service.*;
import com.emm.yixun.estate.service.ProjectRoomServiceFacade;
import com.emm.yixun.marketing.service.HaveAdvanceRefundServiceFacade;
import com.emm.yixun.marketing.service.HaveReceiveRefundServiceFacade;
import com.emm.yixun.marketing.service.NeedAdvanceRefundServiceFacade;
import com.emm.yixun.marketing.service.NeedReceiveRefundServiceFacade;
import com.emm.yixun.system.service.UserServiceFacade;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.emm.yixun.common.excel.FastExcel;
import com.emm.yixun.common.exception.BusinessException;
import com.emm.yixun.common.response.Response;
import com.emm.yixun.common.response.ResponseList;
import com.emm.yixun.common.utils.DateStyle;
import com.emm.yixun.common.utils.DateUtils;
import com.emm.yixun.common.utils.ExcelUtils;
import com.emm.yixun.website.common.ajax.AjaxRes;
import com.emm.yixun.website.common.utils.security.ShiroUtil;
import com.emm.yixun.website.controller.base.BaseController;
import com.emm.yixun.website.vo.CustomerImportVo;


@RequestMapping(value = "/customer/import/")
@Controller
public class CustomerImportController extends BaseController<CustomerModel> {
	
	@Autowired
	private CustomerExcelServiceFacade customerExcelServiceFacade;
	@Autowired
	private CustomerServiceFacade customerServiceFacade;
	@Autowired
	private CustomerErrorServiceFacade customerErrorServiceFacade;
	@Autowired
	private UserServiceFacade userServiceFacade;
	@Autowired
	private ProjectRoomServiceFacade projectRoomServiceFacade;
	@Autowired
	private SubscribeServiceFacade subscribeServiceFacade;
	@Autowired
	private SignedServiceFacade signedServiceFacade;
	@Autowired
	private NeedReceiveRefundServiceFacade needReceiveRefundServiceFacade;
	@Autowired
	private HaveReceiveRefundServiceFacade haveReceiveRefundServiceFacade;
	@Autowired
	private NeedAdvanceRefundServiceFacade needAdvanceRefundServiceFacade;
	@Autowired
	private HaveAdvanceRefundServiceFacade haveAdvanceRefundServiceFacade;

	/**
	* 方法描述 : 导入EXCEL
	* 创建者：ThinkPad 
	* 项目名称： emm-website
	* 类名： CustomerImportController.java
	* 版本： v1.0
	* 创建时间： 2016年5月25日 下午3:39:50
	* @param response
	* @param multipartFile
	* @param request
	* @return java.util.Map<String, String>
	 */
	@RequestMapping(value = "importExcel")
	public @ResponseBody AjaxRes importHouseInfo(HttpServletRequest request,
			@RequestParam("multipartFile")MultipartFile multipartFile,
			HttpServletResponse response,String projectId){
		AjaxRes ar = getAjaxRes();
		response.setContentType("text/html; charset=utf-8");
		try {
			/**
			 * 1.解析Excel数据并转为java实体bean
			 * 2.手机号码表内查重，数据验证(需求编号：406)
			 * 3.验证不通过数据进行日志记录
			 * 4.验证通过数据入库
			 * 5.下载错误数据
			*/
			User user = ShiroUtil.getCurrentUser();
			//解析Excel数据并转为java实体bean
			FastExcel fastExcel=new FastExcel(multipartFile.getInputStream());

//			importSubscribeData(fastExcel);
//			importSignedData(fastExcel);
//			importReceivableData(fastExcel);
//			importReceiptData(fastExcel);
//			importReceiptData2(fastExcel);
//			importReceiptData3(fastExcel);
//			importReceiptData4(fastExcel);
//			importReceiptData5(fastExcel);

			fastExcel.setStartRow(1);
			fastExcel.setSheetName("客户批量导入");
			List<CustomerModel> customerList = fastExcel.parse(CustomerModel.class);
			fastExcel.close();

			//重复行下标
			Map<String,String> repeatMap=findRepeatPhone(customerList);
			CustomerImportVo result=addCustomer(customerList,repeatMap,user,Long.valueOf(projectId));
			ar.setSucceed(result);
			
		} catch (BusinessException e) {
			logger.error(e.toString(), e);
			ar.setFailMsg(e.getErrorMsg());
		} catch (Exception e) {
			logger.error("importHouseInfo error: ", e);
			ar.setFailMsg(e.getMessage());
		}
		return ar;
	}

	public void importReceivableData(FastExcel fastExcel) throws Exception
	{
		for(int i=1; i<fastExcel.getWorkbook().getSheetAt(0).getLastRowNum(); i++){
			Row row = fastExcel.getWorkbook().getSheetAt(0).getRow(i);
			String indexNo = (String) FastExcel.getCellValue(row.getCell(0));
			try {
				String subscribeDate = (String) FastExcel.getCellValue(row.getCell(2));
				if(StringUtils.isBlank(subscribeDate)){
					continue;
				}

				String fundType = (String) FastExcel.getCellValue(row.getCell(21));
				String contractNo = (String) FastExcel.getCellValue(row.getCell(6));
				// 实收定金
				String depositAmount = (String) FastExcel.getCellValue(row.getCell(20));
				// 应收首期
				String firstFund = (String) FastExcel.getCellValue(row.getCell(27));

				String roomName = (String) FastExcel.getCellValue(row.getCell(7));
				roomName = roomName.replaceAll("-1-", "-").replaceAll("-","-->");
				ProjectRoomDto projectRoomDto = new ProjectRoomDto();
				projectRoomDto.setCombinedName(roomName);
				ResponseList<ProjectRoom> roomResponseList = projectRoomServiceFacade.findByDto(projectRoomDto);
				ProjectRoom projectRoom = roomResponseList.getEntity().get(0);

				// 1.车位 2.住宅
				String buildingFormat = roomName.indexOf("车位") > -1? "1":"0";

				String customerName = (String) FastExcel.getCellValue(row.getCell(12));
				String documentNumber = (String) FastExcel.getCellValue(row.getCell(14));
				CustomerDto customerDto = new CustomerDto();
				customerDto.setCustomerName(customerName);
				customerDto.setPeDocumentNumber(documentNumber);
				ResponseList<Customer> customerResponseList = customerServiceFacade.findByDto(customerDto);
				if(customerResponseList.getEntity()==null){
					throw new BusinessException("","客户不存在！");
				}

				// 认购查询
				SubscribeDto subscribeDto = new SubscribeDto();
				subscribeDto.setRoomId(projectRoom.getId());
				subscribeDto.setCustomerId(customerResponseList.getEntity().get(0).getId());
				ResponseList<Subscribe> subscribeResponseList = subscribeServiceFacade.findByDto(subscribeDto);
				if(subscribeResponseList.getEntity()==null){
					throw new BusinessException("","认购不存在！");
				}
				Subscribe subscribe = subscribeResponseList.getEntity().get(0);

				// 签约查询
				SignedDto signedDto = new SignedDto();
				signedDto.setRoomId(projectRoom.getId());
				signedDto.setCustomerId(customerResponseList.getEntity().get(0).getId());
				ResponseList<Signed> signedResponseList = signedServiceFacade.findByDto(signedDto);
				boolean signedFlag = signedResponseList.getEntity()==null;

				List<NeedReceiveRefundDto> needReceiveRefundDtoList = new ArrayList<>();

				if(signedFlag) {
					// 定金应收
					NeedReceiveRefundDto needReceiveRefundDto = new NeedReceiveRefundDto();
					needReceiveRefundDto.setRelationId(subscribe.getId());
					needReceiveRefundDto.setBusinessType(BusinessTypeEnum.SUBSCRIBE.getNo());
					needReceiveRefundDto.setTotalReceivablesAmount(new BigDecimal(depositAmount));
					needReceiveRefundDto.setCustomerId(subscribe.getCustomerId());
					needReceiveRefundDto.setRoomId(subscribe.getRoomId());
					needReceiveRefundDto.setHousingFundType(HousingFundTypeEnum.FUNDTYPE_3.getNo());
					needReceiveRefundDto.setBusinessFlag(BusinessFlagEnum.BUSINESSFLAG_5.getNo());
					needReceiveRefundDto.setStatus(new BigDecimal(depositAmount).compareTo(new BigDecimal(0))==1?"0":"1");
					needReceiveRefundDto.setPostponedStatus("0");
					needReceiveRefundDto.setDocType("0");
					needReceiveRefundDto.setMerchantId(subscribe.getMerchantId());
					needReceiveRefundDto.setAccessCode(subscribe.getAccessCode());
					needReceiveRefundDto.setUserCode(subscribe.getUserCode());
					needReceiveRefundDto.setCreateUserId(1L);
					needReceiveRefundDto.setCreateTime(new Date());
					needReceiveRefundDto.setReceivablesFunds("认购金");
					needReceiveRefundDto.setPaymentMethod(PaymentMethodEnum.LUMP_SUM.getNo());
					needReceiveRefundDto.setRemark("应收数据导入1015");
					needReceiveRefundDto.setDelFlag(DelFlagEnum.UNDEL.getNo());

					needReceiveRefundDtoList.add(needReceiveRefundDto);
				}

				if(new BigDecimal(firstFund).compareTo(new BigDecimal(0))==1) {
					Signed signed = signedResponseList.getEntity().get(0);

					String paymentMethod = ((String) FastExcel.getCellValue(row.getCell(25))).trim();
					String firstFundBalance = (String) FastExcel.getCellValue(row.getCell(29));

					String bankFundAmount = (String) FastExcel.getCellValue(row.getCell(48));
					String bankFundBalance = (String) FastExcel.getCellValue(row.getCell(52));

					// 一期
					String oneFundDate = (String) FastExcel.getCellValue(row.getCell(30));
					String oneFundAmount = (String) FastExcel.getCellValue(row.getCell(31));
					String oneFundBalance = (String) FastExcel.getCellValue(row.getCell(32));
					// 二期
					String secondFundDate = (String) FastExcel.getCellValue(row.getCell(33));
					String secondFundAmount = (String) FastExcel.getCellValue(row.getCell(34));
					String secondFundBalance = (String) FastExcel.getCellValue(row.getCell(35));
					// 三期
					String thirdFundDate = (String) FastExcel.getCellValue(row.getCell(36));
					String thirdFundAmount = (String) FastExcel.getCellValue(row.getCell(37));
					String thirdFundBalance = (String) FastExcel.getCellValue(row.getCell(38));
					// 四期
					String fourthFundDate = (String) FastExcel.getCellValue(row.getCell(39));
					String fourthFundAmount = (String) FastExcel.getCellValue(row.getCell(40));
					String fourthFundBalance = (String) FastExcel.getCellValue(row.getCell(41));

					BigDecimal needFirstFund = new BigDecimal(firstFund);
					if(StringUtils.isNotBlank(oneFundAmount)) {
						needFirstFund = needFirstFund.subtract(new BigDecimal(oneFundAmount));
					}
					if(StringUtils.isNotBlank(secondFundAmount)) {
						needFirstFund = needFirstFund.subtract(new BigDecimal(secondFundAmount));
					}
					if(StringUtils.isNotBlank(thirdFundAmount)) {
						needFirstFund = needFirstFund.subtract(new BigDecimal(thirdFundAmount));
					}
					if(StringUtils.isNotBlank(fourthFundAmount)) {
						needFirstFund = needFirstFund.subtract(new BigDecimal(fourthFundAmount));
					}
					firstFund = needFirstFund.toString();

					switch (paymentMethod) {
						case "车位分期":
							needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, "", firstFund, firstFundBalance, HousingFundTypeEnum.FUNDTYPE_35.getDes(),
									PaymentMethodEnum.CAR_INSTALLMENT_PAYMENT.getNo(), HousingFundTypeEnum.FUNDTYPE_35.getNo()));

							if (StringUtils.isNotBlank(oneFundDate)) {
								needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, oneFundDate, oneFundAmount, oneFundBalance, HousingFundTypeEnum.FUNDTYPE_38.getDes(),
										PaymentMethodEnum.CAR_INSTALLMENT_PAYMENT.getNo(), HousingFundTypeEnum.FUNDTYPE_38.getNo()));
							}
							if (StringUtils.isNotBlank(secondFundDate)) {
								needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, secondFundDate, secondFundAmount, secondFundBalance, HousingFundTypeEnum.FUNDTYPE_39.getDes(),
										PaymentMethodEnum.CAR_INSTALLMENT_PAYMENT.getNo(), HousingFundTypeEnum.FUNDTYPE_39.getNo()));
							}
							if (StringUtils.isNotBlank(thirdFundDate)) {
								needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, thirdFundDate, thirdFundAmount, thirdFundBalance, HousingFundTypeEnum.FUNDTYPE_40.getDes(),
										PaymentMethodEnum.CAR_INSTALLMENT_PAYMENT.getNo(), HousingFundTypeEnum.FUNDTYPE_40.getNo()));
							}
							if (StringUtils.isNotBlank(fourthFundDate)) {
								needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, fourthFundDate, fourthFundAmount, fourthFundBalance, HousingFundTypeEnum.FUNDTYPE_41.getDes(),
										PaymentMethodEnum.CAR_INSTALLMENT_PAYMENT.getNo(), HousingFundTypeEnum.FUNDTYPE_41.getNo()));
							}
							break;
						case "车位一次性付款":
							needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, "", firstFund, firstFundBalance, HousingFundTypeEnum.FUNDTYPE_43.getDes(),
									PaymentMethodEnum.CAR_LUMP_SUM.getNo(), HousingFundTypeEnum.FUNDTYPE_43.getNo()));

							if (StringUtils.isNotBlank(oneFundDate)) {
								needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, oneFundDate, oneFundAmount, oneFundBalance, HousingFundTypeEnum.FUNDTYPE_38.getDes(),
										PaymentMethodEnum.CAR_LUMP_SUM.getNo(), HousingFundTypeEnum.FUNDTYPE_38.getNo()));
							}
							if (StringUtils.isNotBlank(secondFundDate)) {
								needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, secondFundDate, secondFundAmount, secondFundBalance, HousingFundTypeEnum.FUNDTYPE_39.getDes(),
										PaymentMethodEnum.CAR_LUMP_SUM.getNo(), HousingFundTypeEnum.FUNDTYPE_39.getNo()));
							}
							if (StringUtils.isNotBlank(thirdFundDate)) {
								needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, thirdFundDate, thirdFundAmount, thirdFundBalance, HousingFundTypeEnum.FUNDTYPE_40.getDes(),
										PaymentMethodEnum.CAR_LUMP_SUM.getNo(), HousingFundTypeEnum.FUNDTYPE_40.getNo()));
							}
							if (StringUtils.isNotBlank(fourthFundDate)) {
								needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, fourthFundDate, fourthFundAmount, fourthFundBalance, HousingFundTypeEnum.FUNDTYPE_41.getDes(),
										PaymentMethodEnum.CAR_LUMP_SUM.getNo(), HousingFundTypeEnum.FUNDTYPE_41.getNo()));
							}
							break;
						case "车位银行按揭":
							needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, "", firstFund, firstFundBalance, HousingFundTypeEnum.FUNDTYPE_35.getDes(),
									PaymentMethodEnum.CAR_COMMERCIAL_MORTGAGE.getNo(), HousingFundTypeEnum.FUNDTYPE_35.getNo()));

							needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, "", bankFundAmount, bankFundBalance, HousingFundTypeEnum.FUNDTYPE_36.getDes(),
									PaymentMethodEnum.CAR_COMMERCIAL_MORTGAGE.getNo(), HousingFundTypeEnum.FUNDTYPE_36.getNo()));

							if (StringUtils.isNotBlank(oneFundDate)) {
								needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, oneFundDate, oneFundAmount, oneFundBalance, HousingFundTypeEnum.FUNDTYPE_38.getDes(),
										PaymentMethodEnum.CAR_COMMERCIAL_MORTGAGE.getNo(), HousingFundTypeEnum.FUNDTYPE_38.getNo()));
							}
							if (StringUtils.isNotBlank(secondFundDate)) {
								needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, secondFundDate, secondFundAmount, secondFundBalance, HousingFundTypeEnum.FUNDTYPE_39.getDes(),
										PaymentMethodEnum.CAR_COMMERCIAL_MORTGAGE.getNo(), HousingFundTypeEnum.FUNDTYPE_39.getNo()));
							}
							if (StringUtils.isNotBlank(thirdFundDate)) {
								needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, thirdFundDate, thirdFundAmount, thirdFundBalance, HousingFundTypeEnum.FUNDTYPE_40.getDes(),
										PaymentMethodEnum.CAR_COMMERCIAL_MORTGAGE.getNo(), HousingFundTypeEnum.FUNDTYPE_40.getNo()));
							}
							if (StringUtils.isNotBlank(fourthFundDate)) {
								needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, fourthFundDate, fourthFundAmount, fourthFundBalance, HousingFundTypeEnum.FUNDTYPE_41.getDes(),
										PaymentMethodEnum.CAR_COMMERCIAL_MORTGAGE.getNo(), HousingFundTypeEnum.FUNDTYPE_41.getNo()));
							}
							break;
						case "公积金":
							needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, "", firstFund, firstFundBalance, HousingFundTypeEnum.FUNDTYPE_10.getDes(),
									PaymentMethodEnum.PROVIDENT_FUND_MORTGAGE.getNo(), HousingFundTypeEnum.FUNDTYPE_10.getNo()));

							needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, "", bankFundAmount, bankFundBalance, HousingFundTypeEnum.FUNDTYPE_11.getDes(),
									PaymentMethodEnum.PROVIDENT_FUND_MORTGAGE.getNo(), HousingFundTypeEnum.FUNDTYPE_11.getNo()));
							break;
						case "首付分期":
							needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, "", firstFund, firstFundBalance, HousingFundTypeEnum.FUNDTYPE_5.getDes(),
									PaymentMethodEnum.LUMP_SUM.getNo(), HousingFundTypeEnum.FUNDTYPE_5.getNo()));

							if (new BigDecimal(bankFundAmount).compareTo(new BigDecimal(0)) == 1) {
								needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, "", bankFundAmount, bankFundBalance, HousingFundTypeEnum.FUNDTYPE_7.getDes(),
										PaymentMethodEnum.LUMP_SUM.getNo(), HousingFundTypeEnum.FUNDTYPE_7.getNo()));
							}

							if (StringUtils.isNotBlank(oneFundDate)) {
								needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, oneFundDate, oneFundAmount, oneFundBalance, HousingFundTypeEnum.FUNDTYPE_29.getDes(),
										PaymentMethodEnum.LUMP_SUM.getNo(), HousingFundTypeEnum.FUNDTYPE_29.getNo()));
							}
							if (StringUtils.isNotBlank(secondFundDate)) {
								needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, secondFundDate, secondFundAmount, secondFundBalance, HousingFundTypeEnum.FUNDTYPE_30.getDes(),
										PaymentMethodEnum.LUMP_SUM.getNo(), HousingFundTypeEnum.FUNDTYPE_30.getNo()));
							}
							if (StringUtils.isNotBlank(thirdFundDate)) {
								needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, thirdFundDate, thirdFundAmount, thirdFundBalance, HousingFundTypeEnum.FUNDTYPE_31.getDes(),
										PaymentMethodEnum.LUMP_SUM.getNo(), HousingFundTypeEnum.FUNDTYPE_31.getNo()));
							}
							if (StringUtils.isNotBlank(fourthFundDate)) {
								needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, fourthFundDate, fourthFundAmount, fourthFundBalance, HousingFundTypeEnum.FUNDTYPE_32.getDes(),
										PaymentMethodEnum.LUMP_SUM.getNo(), HousingFundTypeEnum.FUNDTYPE_32.getNo()));
							}
							break;
						case "首付分期【公积金】":
							needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, "", firstFund, firstFundBalance, HousingFundTypeEnum.FUNDTYPE_10.getDes(),
									PaymentMethodEnum.PROVIDENT_FUND_MORTGAGE.getNo(), HousingFundTypeEnum.FUNDTYPE_10.getNo()));

							needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, "", bankFundAmount, bankFundBalance, HousingFundTypeEnum.FUNDTYPE_11.getDes(),
									PaymentMethodEnum.PROVIDENT_FUND_MORTGAGE.getNo(), HousingFundTypeEnum.FUNDTYPE_11.getNo()));

							if (StringUtils.isNotBlank(oneFundDate)) {
								needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, oneFundDate, oneFundAmount, oneFundBalance, HousingFundTypeEnum.FUNDTYPE_29.getDes(),
										PaymentMethodEnum.PROVIDENT_FUND_MORTGAGE.getNo(), HousingFundTypeEnum.FUNDTYPE_29.getNo()));
							}
							if (StringUtils.isNotBlank(secondFundDate)) {
								needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, secondFundDate, secondFundAmount, secondFundBalance, HousingFundTypeEnum.FUNDTYPE_30.getDes(),
										PaymentMethodEnum.PROVIDENT_FUND_MORTGAGE.getNo(), HousingFundTypeEnum.FUNDTYPE_30.getNo()));
							}
							if (StringUtils.isNotBlank(thirdFundDate)) {
								needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, thirdFundDate, thirdFundAmount, thirdFundBalance, HousingFundTypeEnum.FUNDTYPE_31.getDes(),
										PaymentMethodEnum.PROVIDENT_FUND_MORTGAGE.getNo(), HousingFundTypeEnum.FUNDTYPE_31.getNo()));
							}
							if (StringUtils.isNotBlank(fourthFundDate)) {
								needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, fourthFundDate, fourthFundAmount, fourthFundBalance, HousingFundTypeEnum.FUNDTYPE_32.getDes(),
										PaymentMethodEnum.PROVIDENT_FUND_MORTGAGE.getNo(), HousingFundTypeEnum.FUNDTYPE_32.getNo()));
							}
							break;
						case "首付分期【银行按揭】":
							needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, "", firstFund, firstFundBalance, HousingFundTypeEnum.FUNDTYPE_6.getDes(),
									PaymentMethodEnum.COMMERCIAL_MORTGAGE.getNo(), HousingFundTypeEnum.FUNDTYPE_6.getNo()));

							needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, "", bankFundAmount, bankFundBalance, HousingFundTypeEnum.FUNDTYPE_7.getDes(),
									PaymentMethodEnum.COMMERCIAL_MORTGAGE.getNo(), HousingFundTypeEnum.FUNDTYPE_7.getNo()));

							if (StringUtils.isNotBlank(oneFundDate)) {
								needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, oneFundDate, oneFundAmount, oneFundBalance, HousingFundTypeEnum.FUNDTYPE_29.getDes(),
										PaymentMethodEnum.COMMERCIAL_MORTGAGE.getNo(), HousingFundTypeEnum.FUNDTYPE_29.getNo()));
							}
							if (StringUtils.isNotBlank(secondFundDate)) {
								needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, secondFundDate, secondFundAmount, secondFundBalance, HousingFundTypeEnum.FUNDTYPE_30.getDes(),
										PaymentMethodEnum.COMMERCIAL_MORTGAGE.getNo(), HousingFundTypeEnum.FUNDTYPE_30.getNo()));
							}
							if (StringUtils.isNotBlank(thirdFundDate)) {
								needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, thirdFundDate, thirdFundAmount, thirdFundBalance, HousingFundTypeEnum.FUNDTYPE_31.getDes(),
										PaymentMethodEnum.COMMERCIAL_MORTGAGE.getNo(), HousingFundTypeEnum.FUNDTYPE_31.getNo()));
							}
							if (StringUtils.isNotBlank(fourthFundDate)) {
								needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, fourthFundDate, fourthFundAmount, fourthFundBalance, HousingFundTypeEnum.FUNDTYPE_32.getDes(),
										PaymentMethodEnum.COMMERCIAL_MORTGAGE.getNo(), HousingFundTypeEnum.FUNDTYPE_32.getNo()));
							}
							break;
						case "首付分期【组合贷】":
							needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, "", firstFund, firstFundBalance, HousingFundTypeEnum.FUNDTYPE_17.getDes(),
									PaymentMethodEnum.COMBINED_LOAN.getNo(), HousingFundTypeEnum.FUNDTYPE_17.getNo()));

							needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, "", bankFundAmount, bankFundBalance, HousingFundTypeEnum.FUNDTYPE_18.getDes(),
									PaymentMethodEnum.COMBINED_LOAN.getNo(), HousingFundTypeEnum.FUNDTYPE_18.getNo()));

							if (StringUtils.isNotBlank(oneFundDate)) {
								needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, oneFundDate, oneFundAmount, oneFundBalance, HousingFundTypeEnum.FUNDTYPE_29.getDes(),
										PaymentMethodEnum.COMBINED_LOAN.getNo(), HousingFundTypeEnum.FUNDTYPE_29.getNo()));
							}
							if (StringUtils.isNotBlank(secondFundDate)) {
								needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, secondFundDate, secondFundAmount, secondFundBalance, HousingFundTypeEnum.FUNDTYPE_30.getDes(),
										PaymentMethodEnum.COMBINED_LOAN.getNo(), HousingFundTypeEnum.FUNDTYPE_30.getNo()));
							}
							if (StringUtils.isNotBlank(thirdFundDate)) {
								needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, thirdFundDate, thirdFundAmount, thirdFundBalance, HousingFundTypeEnum.FUNDTYPE_31.getDes(),
										PaymentMethodEnum.COMBINED_LOAN.getNo(), HousingFundTypeEnum.FUNDTYPE_31.getNo()));
							}
							if (StringUtils.isNotBlank(fourthFundDate)) {
								needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, fourthFundDate, fourthFundAmount, fourthFundBalance, HousingFundTypeEnum.FUNDTYPE_32.getDes(),
										PaymentMethodEnum.COMBINED_LOAN.getNo(), HousingFundTypeEnum.FUNDTYPE_32.getNo()));
							}
							break;
						case "一次性付款":
							needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, "", firstFund, firstFundBalance, HousingFundTypeEnum.FUNDTYPE_5.getDes(),
									PaymentMethodEnum.LUMP_SUM.getNo(), HousingFundTypeEnum.FUNDTYPE_5.getNo()));
							break;
						case "银行按揭":
							needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, "", firstFund, firstFundBalance, HousingFundTypeEnum.FUNDTYPE_6.getDes(),
									PaymentMethodEnum.COMMERCIAL_MORTGAGE.getNo(), HousingFundTypeEnum.FUNDTYPE_6.getNo()));

							needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, "", bankFundAmount, bankFundBalance, HousingFundTypeEnum.FUNDTYPE_7.getDes(),
									PaymentMethodEnum.COMMERCIAL_MORTGAGE.getNo(), HousingFundTypeEnum.FUNDTYPE_7.getNo()));
							break;
						case "组合贷":
							needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, "", firstFund, firstFundBalance, HousingFundTypeEnum.FUNDTYPE_17.getDes(),
									PaymentMethodEnum.COMBINED_LOAN.getNo(), HousingFundTypeEnum.FUNDTYPE_17.getNo()));

							needReceiveRefundDtoList.add(getNeedReceiveRefundDto(signed, "", bankFundAmount, bankFundBalance, HousingFundTypeEnum.FUNDTYPE_18.getDes(),
									PaymentMethodEnum.COMBINED_LOAN.getNo(), HousingFundTypeEnum.FUNDTYPE_18.getNo()));
							break;
					}
				}

				subscribeServiceFacade.addNeedReceiveRefundImport(needReceiveRefundDtoList);
			}catch (BusinessException be){
				System.out.println("BusinessException: "+indexNo);
			}catch (Exception e){
				System.out.println(indexNo);
			}
		}
	}

	public NeedReceiveRefundDto getNeedReceiveRefundDto(Signed signed, String receivablesDate,
			String fundAmount, String fundBalance, String fundType, String paymentMethod, String housingFundType) throws Exception
	{
		NeedReceiveRefundDto needReceiveRefundDto = new NeedReceiveRefundDto();
		needReceiveRefundDto.setRelationId(signed.getId());
		needReceiveRefundDto.setBusinessType(BusinessTypeEnum.SIGNED.getNo());
		if(StringUtils.isNotBlank(receivablesDate)) {
			needReceiveRefundDto.setReceivablesDate(DateUtils.stringToDate(receivablesDate, DateUtils.parsePatterns[1]));
		}
		needReceiveRefundDto.setTotalReceivablesAmount(new BigDecimal(fundAmount));
		needReceiveRefundDto.setCustomerId(signed.getCustomerId());
		needReceiveRefundDto.setRoomId(signed.getRoomId());
		needReceiveRefundDto.setHousingFundType(housingFundType);
		needReceiveRefundDto.setBusinessFlag(BusinessFlagEnum.BUSINESSFLAG_9.getNo());
		needReceiveRefundDto.setStatus(new BigDecimal(fundBalance).compareTo(new BigDecimal(0)) == 1 ? "1" : "0");
		needReceiveRefundDto.setPostponedStatus("0");
		needReceiveRefundDto.setDocType("0");
		needReceiveRefundDto.setMerchantId(signed.getMerchantId());
		needReceiveRefundDto.setAccessCode(signed.getAccessCode());
		needReceiveRefundDto.setUserCode(signed.getUserCode());
		needReceiveRefundDto.setCreateUserId(1L);
		needReceiveRefundDto.setCreateTime(new Date());
		needReceiveRefundDto.setReceivablesFunds(fundType);
		needReceiveRefundDto.setPaymentMethod(paymentMethod);
		needReceiveRefundDto.setRemark("应收数据导入1015");
		needReceiveRefundDto.setDelFlag(DelFlagEnum.UNDEL.getNo());

		return needReceiveRefundDto;
	}

	public void importReceiptData(FastExcel fastExcel) throws Exception
	{
		for(int i=6; i<fastExcel.getWorkbook().getSheetAt(0).getLastRowNum(); i++){
			Row row = fastExcel.getWorkbook().getSheetAt(0).getRow(i);
			String indexNo = (String) FastExcel.getCellValue(row.getCell(0));
			try {
				String paymentMethod = (String) FastExcel.getCellValue(row.getCell(21));
				if("预约金".equals(paymentMethod)||"垫付首期".equals(paymentMethod)){
					continue;
				}

				HaveReceiveRefundDto haveReceiveRefundDto = new HaveReceiveRefundDto();

				String projectName = (String) FastExcel.getCellValue(row.getCell(1));
				String buildingName = (String) FastExcel.getCellValue(row.getCell(2));
				String roomName = (String) FastExcel.getCellValue(row.getCell(4));
				roomName = (projectName+"-"+buildingName+"-"+roomName).replaceAll("-","-->");
				ProjectRoomDto projectRoomDto = new ProjectRoomDto();
				projectRoomDto.setCombinedName(roomName);
				ResponseList<ProjectRoom> roomResponseList = projectRoomServiceFacade.findByDto(projectRoomDto);
				ProjectRoom projectRoom = roomResponseList.getEntity().get(0);

				// 客户查询
				String customerName = ((String) FastExcel.getCellValue(row.getCell(17))).trim();
				if(customerName.indexOf(";")>-1){
					customerName = customerName.split(";")[0];
				}
				CustomerDto customerDto = new CustomerDto();
				customerDto.setCustomerName(customerName);
				ResponseList<Customer> customerResponseList = customerServiceFacade.findByDto(customerDto);

				// 认购查询
				SubscribeDto subscribeDto = new SubscribeDto();
				subscribeDto.setRoomId(projectRoom.getId());
				subscribeDto.setCustomerId(customerResponseList.getEntity().get(0).getId());
				ResponseList<Subscribe> subscribeResponseList = subscribeServiceFacade.findByDto(subscribeDto);
				if(subscribeResponseList.getEntity()==null){
					throw new BusinessException("","认购不存在！");
				}
				Subscribe subscribe = subscribeResponseList.getEntity().get(0);

				haveReceiveRefundDto.setCustomerId(subscribe.getCustomerId());
				haveReceiveRefundDto.setRoomId(subscribe.getRoomId());

				// 签约查询
				SignedDto signedDto = new SignedDto();
				signedDto.setRoomId(projectRoom.getId());
				signedDto.setCustomerId(customerResponseList.getEntity().get(0).getId());
				signedDto.setBusinessStatus("1");
				ResponseList<Signed> signedResponseList = signedServiceFacade.findByDto(signedDto);

				String billType = (String) FastExcel.getCellValue(row.getCell(15));
				billType = "收据".equals(billType)?"2":"1";
				haveReceiveRefundDto.setBillType(billType);
				String payerName = (String) FastExcel.getCellValue(row.getCell(17));
				haveReceiveRefundDto.setPayerName(payerName);
				String bankName = (String) FastExcel.getCellValue(row.getCell(24));
				String bankCode = StringUtils.isBlank(bankName)?"":BankNameEnum.getByDescription(bankName).getNo();
				haveReceiveRefundDto.setBankCode(bankCode);
				BigDecimal realAmount = new BigDecimal((String) FastExcel.getCellValue(row.getCell(22)));
				boolean negativeFlag = realAmount.compareTo(new BigDecimal(0))==1;
				haveReceiveRefundDto.setDocType(negativeFlag?"0":"1");
				haveReceiveRefundDto.setRealAmount(new BigDecimal(Math.abs(realAmount.doubleValue())));
				String billCode = (String) FastExcel.getCellValue(row.getCell(16));
				haveReceiveRefundDto.setBillCode(billCode);
				haveReceiveRefundDto.setDeductibleAmount(BigDecimal.ZERO);
				String statementType = (String) FastExcel.getCellValue(row.getCell(23));
				switch (statementType){
					case "POS机":
						statementType = StatementTypeEnum.POS_TRANSFER.getNo();
						break;
					case "现金":
						statementType = StatementTypeEnum.CASH.getNo();
						break;
					case "折扣":
						statementType = StatementTypeEnum.DISCOUNT.getNo();
						break;
					case "转账":
						statementType = StatementTypeEnum.TRANSFER.getNo();
						break;
				}
				haveReceiveRefundDto.setStatementType(Long.parseLong(statementType));
				String statementDate = (String) FastExcel.getCellValue(row.getCell(13));
				haveReceiveRefundDto.setStatementDate(DateUtils.StringToDate(statementDate, DateUtils.parsePatterns[1]));

				NeedReceiveRefundDto nrrDto = new NeedReceiveRefundDto();
				if("定金".equals(paymentMethod)||signedResponseList.getEntity()==null){
					haveReceiveRefundDto.setRelationId(subscribe.getId());
					haveReceiveRefundDto.setPaymentMethod(subscribe.getPaymentMethod());
					haveReceiveRefundDto.setReceivablesType("1");
					haveReceiveRefundDto.setMerchantId(subscribe.getMerchantId());
					haveReceiveRefundDto.setAccessCode(subscribe.getAccessCode());
					haveReceiveRefundDto.setUserCode(subscribe.getUserCode());

					nrrDto.setRelationId(subscribe.getId());
					nrrDto.setBusinessType(BusinessTypeEnum.SUBSCRIBE.getNo());
				}else{
					Signed signed = signedResponseList.getEntity().get(0);
					haveReceiveRefundDto.setRelationId(signed.getId());
					haveReceiveRefundDto.setPaymentMethod(signed.getPaymentMethod());
					haveReceiveRefundDto.setReceivablesType("2");
					haveReceiveRefundDto.setMerchantId(signed.getMerchantId());
					haveReceiveRefundDto.setAccessCode(signed.getAccessCode());
					haveReceiveRefundDto.setUserCode(signed.getUserCode());

					nrrDto.setRelationId(signed.getId());
					nrrDto.setBusinessType(BusinessTypeEnum.SIGNED.getNo());
				}
				nrrDto.setDocType(DocTypeEnum.COLLECTION.getNo());
				ResponseList<NeedReceiveRefund> needReceiveRefundResponseList = needReceiveRefundServiceFacade.findByDto(nrrDto);


				String housingFundType = "";
				if(signedResponseList.getEntity()!=null && "1".equals(signedResponseList.getEntity().get(0).getBusinessStatus())) {
					switch (paymentMethod) {
						case "第一笔首期":
							switch (haveReceiveRefundDto.getPaymentMethod()) {
								case "1":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_29.getNo();
									break;
								case "3":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_29.getNo();
									break;
								case "4":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_29.getNo();
									break;
								case "5":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_29.getNo();
									break;
								case "7":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_38.getNo();
									break;
								case "8":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_38.getNo();
									break;
							}
							haveReceiveRefundDto.setBusinessFlag(negativeFlag ? BusinessFlagEnum.BUSINESSFLAG_9.getNo() : BusinessFlagEnum.BUSINESSFLAG_14.getNo());
							break;
						case "第二笔首期":
							switch (haveReceiveRefundDto.getPaymentMethod()) {
								case "1":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_30.getNo();
									break;
								case "3":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_30.getNo();
									break;
								case "4":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_30.getNo();
									break;
								case "5":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_30.getNo();
									break;
								case "7":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_39.getNo();
									break;
								case "8":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_39.getNo();
									break;
							}
							haveReceiveRefundDto.setBusinessFlag(negativeFlag ? BusinessFlagEnum.BUSINESSFLAG_9.getNo() : BusinessFlagEnum.BUSINESSFLAG_14.getNo());
							break;
						case "第三笔首期":
							switch (haveReceiveRefundDto.getPaymentMethod()) {
								case "1":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_31.getNo();
									break;
								case "3":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_31.getNo();
									break;
								case "4":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_31.getNo();
									break;
								case "5":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_31.getNo();
									break;
								case "7":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_40.getNo();
									break;
								case "8":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_40.getNo();
									break;
							}
							haveReceiveRefundDto.setBusinessFlag(negativeFlag ? BusinessFlagEnum.BUSINESSFLAG_9.getNo() : BusinessFlagEnum.BUSINESSFLAG_14.getNo());
							break;
						case "第四笔首期":
							switch (haveReceiveRefundDto.getPaymentMethod()) {
								case "1":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_32.getNo();
									break;
								case "3":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_32.getNo();
									break;
								case "4":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_32.getNo();
									break;
								case "5":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_32.getNo();
									break;
								case "7":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_41.getNo();
									break;
								case "8":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_41.getNo();
									break;
							}
							haveReceiveRefundDto.setBusinessFlag(negativeFlag ? BusinessFlagEnum.BUSINESSFLAG_9.getNo() : BusinessFlagEnum.BUSINESSFLAG_14.getNo());
							break;
						case "定金":
							housingFundType = HousingFundTypeEnum.FUNDTYPE_3.getNo();
							haveReceiveRefundDto.setBusinessFlag(negativeFlag ? BusinessFlagEnum.BUSINESSFLAG_5.getNo() : BusinessFlagEnum.BUSINESSFLAG_8.getNo());
							break;
						case "公积金":
							housingFundType = HousingFundTypeEnum.FUNDTYPE_11.getNo();
							haveReceiveRefundDto.setBusinessFlag(negativeFlag ? BusinessFlagEnum.BUSINESSFLAG_9.getNo() : BusinessFlagEnum.BUSINESSFLAG_14.getNo());
							break;
						case "楼款":
							housingFundType = HousingFundTypeEnum.FUNDTYPE_5.getNo();
							haveReceiveRefundDto.setBusinessFlag(negativeFlag ? BusinessFlagEnum.BUSINESSFLAG_9.getNo() : BusinessFlagEnum.BUSINESSFLAG_14.getNo());
							break;
						case "首期":
							switch (haveReceiveRefundDto.getPaymentMethod()) {
								case "1":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_5.getNo();
									break;
								case "3":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_6.getNo();
									break;
								case "4":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_10.getNo();
									break;
								case "5":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_17.getNo();
									break;
								case "6":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_43.getNo();
									break;
								case "7":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_35.getNo();
									break;
								case "8":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_35.getNo();
									break;
							}
							haveReceiveRefundDto.setBusinessFlag(negativeFlag ? BusinessFlagEnum.BUSINESSFLAG_9.getNo() : BusinessFlagEnum.BUSINESSFLAG_14.getNo());
							break;
						case "挞定款":
							housingFundType = HousingFundTypeEnum.FUNDTYPE_3.getNo();
							haveReceiveRefundDto.setBusinessFlag(negativeFlag ? BusinessFlagEnum.BUSINESSFLAG_5.getNo() : BusinessFlagEnum.BUSINESSFLAG_8.getNo());
							break;
						case "现金折扣":
							housingFundType = HousingFundTypeEnum.FUNDTYPE_44.getNo();
							haveReceiveRefundDto.setBusinessFlag(negativeFlag ? BusinessFlagEnum.BUSINESSFLAG_9.getNo() : BusinessFlagEnum.BUSINESSFLAG_14.getNo());
							break;
						case "银行按揭":
							switch (haveReceiveRefundDto.getPaymentMethod()) {
								case "1":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_7.getNo();
									break;
								case "3":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_7.getNo();
									break;
								case "4":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_11.getNo();
									break;
								case "5":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_18.getNo();
									break;
								case "8":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_36.getNo();
									break;
							}
							haveReceiveRefundDto.setBusinessFlag(negativeFlag ? BusinessFlagEnum.BUSINESSFLAG_9.getNo() : BusinessFlagEnum.BUSINESSFLAG_14.getNo());
							break;
					}
					if (negativeFlag) {
						List<String> fundTypeList = Arrays.asList("3", "5");
						String fundType = housingFundType;
						if (fundTypeList.contains(housingFundType)) {
							switch (haveReceiveRefundDto.getPaymentMethod()) {
								case "1":
									fundType = HousingFundTypeEnum.FUNDTYPE_5.getNo();
									break;
								case "3":
									fundType = HousingFundTypeEnum.FUNDTYPE_6.getNo();
									break;
								case "4":
									fundType = HousingFundTypeEnum.FUNDTYPE_10.getNo();
									break;
								case "5":
									fundType = HousingFundTypeEnum.FUNDTYPE_17.getNo();
									break;
								case "6":
									fundType = HousingFundTypeEnum.FUNDTYPE_43.getNo();
									break;
								case "7":
									fundType = HousingFundTypeEnum.FUNDTYPE_35.getNo();
									break;
								case "8":
									fundType = HousingFundTypeEnum.FUNDTYPE_35.getNo();
									break;
							}
						} else if ("11".equals(housingFundType)) {
							switch (haveReceiveRefundDto.getPaymentMethod()) {
								case "4":
									fundType = HousingFundTypeEnum.FUNDTYPE_11.getNo();
									break;
								case "5":
									fundType = HousingFundTypeEnum.FUNDTYPE_18.getNo();
									break;
							}
						}
						for (NeedReceiveRefund receiveRefund : needReceiveRefundResponseList.getEntity()) {
							if (receiveRefund.getHousingFundType().equals(fundType)) {
								haveReceiveRefundDto.setReceivablesId(receiveRefund.getId());
								break;
							}
						}
					} else {
						NeedReceiveRefundDto needReceiveRefundDto = new NeedReceiveRefundDto();
						needReceiveRefundDto.setRelationId(signedResponseList.getEntity() == null ? subscribe.getId() : signedResponseList.getEntity().get(0).getId());
						needReceiveRefundDto.setBusinessType(BusinessTypeEnum.SIGNED.getNo());
						needReceiveRefundDto.setReceivablesDate(haveReceiveRefundDto.getStatementDate());
						needReceiveRefundDto.setTotalReceivablesAmount(haveReceiveRefundDto.getRealAmount());
						needReceiveRefundDto.setCustomerId(signedResponseList.getEntity() == null ? subscribe.getCustomerId() : signedResponseList.getEntity().get(0).getCustomerId());
						needReceiveRefundDto.setRoomId(signedResponseList.getEntity() == null ? subscribe.getRoomId() : signedResponseList.getEntity().get(0).getRoomId());
						needReceiveRefundDto.setHousingFundType(housingFundType);
						needReceiveRefundDto.setBusinessFlag(BusinessFlagEnum.BUSINESSFLAG_9.getNo());
						needReceiveRefundDto.setStatus("1");
						needReceiveRefundDto.setPostponedStatus("0");
						needReceiveRefundDto.setDocType("1");
						needReceiveRefundDto.setMerchantId(signedResponseList.getEntity() == null ? subscribe.getMerchantId() : signedResponseList.getEntity().get(0).getMerchantId());
						needReceiveRefundDto.setAccessCode(signedResponseList.getEntity() == null ? subscribe.getAccessCode() : signedResponseList.getEntity().get(0).getAccessCode());
						needReceiveRefundDto.setUserCode(signedResponseList.getEntity() == null ? subscribe.getUserCode() : signedResponseList.getEntity().get(0).getUserCode());
						needReceiveRefundDto.setCreateUserId(1L);
						needReceiveRefundDto.setCreateTime(new Date());
						needReceiveRefundDto.setReceivablesFunds(HousingFundTypeEnum.getByNo(housingFundType).getDes());
						needReceiveRefundDto.setPaymentMethod(haveReceiveRefundDto.getPaymentMethod());
						needReceiveRefundDto.setRemark("实收数据导入1013_" + indexNo);
						needReceiveRefundDto.setDelFlag(DelFlagEnum.UNDEL.getNo());

						Response<NeedReceiveRefundDto> needReceiveRefundDtoResponse = needReceiveRefundServiceFacade.save(needReceiveRefundDto);
						haveReceiveRefundDto.setReceivablesId(needReceiveRefundDtoResponse.getEntity().getId());
					}
					haveReceiveRefundDto.setHousingFundType(housingFundType);
				// 绑定认购
				}else{
					NeedReceiveRefundDto needReceiveRefundDto = new NeedReceiveRefundDto();
					needReceiveRefundDto.setRelationId(subscribe.getId());
					needReceiveRefundDto.setBusinessType(BusinessTypeEnum.SUBSCRIBE.getNo());
					needReceiveRefundDto.setReceivablesDate(haveReceiveRefundDto.getStatementDate());
					needReceiveRefundDto.setTotalReceivablesAmount(haveReceiveRefundDto.getRealAmount());
					needReceiveRefundDto.setCustomerId(subscribe.getCustomerId());
					needReceiveRefundDto.setRoomId(subscribe.getRoomId());
					needReceiveRefundDto.setHousingFundType(negativeFlag?HousingFundTypeEnum.FUNDTYPE_3.getNo():HousingFundTypeEnum.FUNDTYPE_4.getNo());
					needReceiveRefundDto.setBusinessFlag(negativeFlag?BusinessFlagEnum.BUSINESSFLAG_5.getNo():BusinessFlagEnum.BUSINESSFLAG_8.getNo());
					needReceiveRefundDto.setStatus("1");
					needReceiveRefundDto.setPostponedStatus("0");
					needReceiveRefundDto.setDocType(negativeFlag?"0":"1");
					needReceiveRefundDto.setMerchantId(subscribe.getMerchantId());
					needReceiveRefundDto.setAccessCode(subscribe.getAccessCode());
					needReceiveRefundDto.setUserCode(subscribe.getUserCode());
					needReceiveRefundDto.setCreateUserId(1L);
					needReceiveRefundDto.setCreateTime(new Date());
					needReceiveRefundDto.setReceivablesFunds(negativeFlag?BusinessFlagEnum.BUSINESSFLAG_5.getDes():BusinessFlagEnum.BUSINESSFLAG_8.getDes());
					needReceiveRefundDto.setPaymentMethod(haveReceiveRefundDto.getPaymentMethod());
					needReceiveRefundDto.setRemark("实收数据导入1013_" + indexNo);
					needReceiveRefundDto.setDelFlag(DelFlagEnum.UNDEL.getNo());

					Response<NeedReceiveRefundDto> needReceiveRefundDtoResponse = needReceiveRefundServiceFacade.save(needReceiveRefundDto);
					haveReceiveRefundDto.setReceivablesId(needReceiveRefundDtoResponse.getEntity().getId());
					haveReceiveRefundDto.setBusinessFlag(negativeFlag ? BusinessFlagEnum.BUSINESSFLAG_5.getNo() : BusinessFlagEnum.BUSINESSFLAG_8.getNo());
					haveReceiveRefundDto.setHousingFundType(needReceiveRefundDtoResponse.getEntity().getHousingFundType());
				}

				haveReceiveRefundDto.setTransferStatus("0");
				haveReceiveRefundDto.setDelFlag(DelFlagEnum.UNDEL.getNo());
				haveReceiveRefundDto.setCreateUserId(1L);
				haveReceiveRefundDto.setCreateTime(new Date());
				haveReceiveRefundDto.setRemark("实收数据导入1013_"+indexNo);

				subscribeServiceFacade.addHaveReceiveRefundImport(haveReceiveRefundDto);
			}catch (BusinessException be){

			}catch (Exception e){
				System.out.println(indexNo);
			}
		}
	}

	public void importReceiptData2(FastExcel fastExcel) throws Exception
	{
		for(int i=2; i<fastExcel.getWorkbook().getSheetAt(0).getLastRowNum(); i++){
			Row row = fastExcel.getWorkbook().getSheetAt(0).getRow(i);
			String indexNo = (String) FastExcel.getCellValue(row.getCell(0));
			try {
				String paymentMethod = (String) FastExcel.getCellValue(row.getCell(22));
				if("预约金".equals(paymentMethod)||"垫付首期".equals(paymentMethod)){
					continue;
				}

				HaveReceiveRefundDto haveReceiveRefundDto = new HaveReceiveRefundDto();

				String projectName = (String) FastExcel.getCellValue(row.getCell(2));
				String buildingName = (String) FastExcel.getCellValue(row.getCell(3));
				String roomName = (String) FastExcel.getCellValue(row.getCell(5));
				roomName = (projectName+"-"+buildingName+"-"+roomName).replaceAll("-","-->");
				ProjectRoomDto projectRoomDto = new ProjectRoomDto();
				projectRoomDto.setCombinedName(roomName);
				ResponseList<ProjectRoom> roomResponseList = projectRoomServiceFacade.findByDto(projectRoomDto);
				ProjectRoom projectRoom = roomResponseList.getEntity().get(0);

				// 客户查询
				String customerName = ((String) FastExcel.getCellValue(row.getCell(19))).trim();
				if(customerName.indexOf(";")>-1){
					customerName = customerName.split(";")[0];
				}
				CustomerDto customerDto = new CustomerDto();
				customerDto.setCustomerName(customerName);
				if("陈丽".equals(customerName)){
					customerDto.setPeDocumentNumber("430921198406116167");
				}
				ResponseList<Customer> customerResponseList = customerServiceFacade.findByDto(customerDto);

				// 认购查询
				SubscribeDto subscribeDto = new SubscribeDto();
				subscribeDto.setRoomId(projectRoom.getId());
				subscribeDto.setCustomerId(customerResponseList.getEntity().get(0).getId());
				ResponseList<Subscribe> subscribeResponseList = subscribeServiceFacade.findByDto(subscribeDto);
				if(subscribeResponseList.getEntity()==null){
					throw new BusinessException("","认购不存在！");
				}
				Subscribe subscribe = subscribeResponseList.getEntity().get(0);

				haveReceiveRefundDto.setCustomerId(subscribe.getCustomerId());
				haveReceiveRefundDto.setRoomId(subscribe.getRoomId());

				// 签约查询
				SignedDto signedDto = new SignedDto();
				signedDto.setRoomId(projectRoom.getId());
				signedDto.setCustomerId(customerResponseList.getEntity().get(0).getId());
				signedDto.setAuditStatus("3");
				ResponseList<Signed> signedResponseList = signedServiceFacade.findByDto(signedDto);

				String billType = (String) FastExcel.getCellValue(row.getCell(16));
				billType = "收据".equals(billType)?"2":"1";
				haveReceiveRefundDto.setBillType(billType);
				String payerName = (String) FastExcel.getCellValue(row.getCell(18));
				haveReceiveRefundDto.setPayerName(payerName);
				String bankName = (String) FastExcel.getCellValue(row.getCell(25));
				String bankCode = StringUtils.isBlank(bankName)?"":BankNameEnum.getByDescription(bankName).getNo();
				haveReceiveRefundDto.setBankCode(bankCode);
				BigDecimal realAmount = new BigDecimal((String) FastExcel.getCellValue(row.getCell(23)));
				boolean negativeFlag = realAmount.compareTo(new BigDecimal(0))==1;
				haveReceiveRefundDto.setDocType(negativeFlag?"0":"1");
				haveReceiveRefundDto.setRealAmount(new BigDecimal(Math.abs(realAmount.doubleValue())));
				String billCode = (String) FastExcel.getCellValue(row.getCell(17));
				haveReceiveRefundDto.setBillCode(billCode);
				haveReceiveRefundDto.setDeductibleAmount(BigDecimal.ZERO);
				String statementType = (String) FastExcel.getCellValue(row.getCell(24));
				switch (statementType){
					case "POS机":
						statementType = StatementTypeEnum.POS_TRANSFER.getNo();
						break;
					case "现金":
						statementType = StatementTypeEnum.CASH.getNo();
						break;
					case "折扣":
						statementType = StatementTypeEnum.DISCOUNT.getNo();
						break;
					case "转账":
						statementType = StatementTypeEnum.TRANSFER.getNo();
						break;
				}
				haveReceiveRefundDto.setStatementType(Long.parseLong(statementType));
				String statementDate = (String) FastExcel.getCellValue(row.getCell(14));
				haveReceiveRefundDto.setStatementDate(DateUtils.StringToDate(statementDate, DateUtils.parsePatterns[1]));

				NeedReceiveRefundDto nrrDto = new NeedReceiveRefundDto();
				if(signedResponseList.getEntity()==null){
					haveReceiveRefundDto.setRelationId(subscribe.getId());
					haveReceiveRefundDto.setPaymentMethod(subscribe.getPaymentMethod());
					haveReceiveRefundDto.setReceivablesType("1");
					haveReceiveRefundDto.setMerchantId(subscribe.getMerchantId());
					haveReceiveRefundDto.setAccessCode(subscribe.getAccessCode());
					haveReceiveRefundDto.setUserCode(subscribe.getUserCode());

					nrrDto.setRelationId(subscribe.getId());
					nrrDto.setBusinessType(BusinessTypeEnum.SUBSCRIBE.getNo());
				}else{
					Signed signed = signedResponseList.getEntity().get(0);
					haveReceiveRefundDto.setRelationId(signed.getId());
					haveReceiveRefundDto.setPaymentMethod(signed.getPaymentMethod());
					haveReceiveRefundDto.setReceivablesType("2");
					haveReceiveRefundDto.setMerchantId(signed.getMerchantId());
					haveReceiveRefundDto.setAccessCode(signed.getAccessCode());
					haveReceiveRefundDto.setUserCode(signed.getUserCode());

					nrrDto.setRelationId(signed.getId());
					nrrDto.setBusinessType(BusinessTypeEnum.SIGNED.getNo());
				}
				nrrDto.setDocType(DocTypeEnum.COLLECTION.getNo());
				ResponseList<NeedReceiveRefund> needReceiveRefundResponseList = needReceiveRefundServiceFacade.findByDto(nrrDto);


				String housingFundType = "";
				if(signedResponseList.getEntity()!=null && "1".equals(signedResponseList.getEntity().get(0).getBusinessStatus())) {
					switch (paymentMethod) {
						case "第一笔首期":
							switch (haveReceiveRefundDto.getPaymentMethod()) {
								case "1":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_29.getNo();
									break;
								case "3":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_29.getNo();
									break;
								case "4":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_29.getNo();
									break;
								case "5":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_29.getNo();
									break;
								case "7":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_38.getNo();
									break;
								case "8":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_38.getNo();
									break;
							}
							haveReceiveRefundDto.setBusinessFlag(negativeFlag ? BusinessFlagEnum.BUSINESSFLAG_9.getNo() : BusinessFlagEnum.BUSINESSFLAG_14.getNo());
							break;
						case "第二笔首期":
							switch (haveReceiveRefundDto.getPaymentMethod()) {
								case "1":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_30.getNo();
									break;
								case "3":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_30.getNo();
									break;
								case "4":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_30.getNo();
									break;
								case "5":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_30.getNo();
									break;
								case "7":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_39.getNo();
									break;
								case "8":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_39.getNo();
									break;
							}
							haveReceiveRefundDto.setBusinessFlag(negativeFlag ? BusinessFlagEnum.BUSINESSFLAG_9.getNo() : BusinessFlagEnum.BUSINESSFLAG_14.getNo());
							break;
						case "第三笔首期":
							switch (haveReceiveRefundDto.getPaymentMethod()) {
								case "1":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_31.getNo();
									break;
								case "3":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_31.getNo();
									break;
								case "4":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_31.getNo();
									break;
								case "5":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_31.getNo();
									break;
								case "7":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_40.getNo();
									break;
								case "8":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_40.getNo();
									break;
							}
							haveReceiveRefundDto.setBusinessFlag(negativeFlag ? BusinessFlagEnum.BUSINESSFLAG_9.getNo() : BusinessFlagEnum.BUSINESSFLAG_14.getNo());
							break;
						case "第四笔首期":
							switch (haveReceiveRefundDto.getPaymentMethod()) {
								case "1":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_32.getNo();
									break;
								case "3":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_32.getNo();
									break;
								case "4":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_32.getNo();
									break;
								case "5":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_32.getNo();
									break;
								case "7":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_41.getNo();
									break;
								case "8":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_41.getNo();
									break;
							}
							haveReceiveRefundDto.setBusinessFlag(negativeFlag ? BusinessFlagEnum.BUSINESSFLAG_9.getNo() : BusinessFlagEnum.BUSINESSFLAG_14.getNo());
							break;
						case "定金":
							housingFundType = HousingFundTypeEnum.FUNDTYPE_3.getNo();
							haveReceiveRefundDto.setBusinessFlag(negativeFlag ? BusinessFlagEnum.BUSINESSFLAG_5.getNo() : BusinessFlagEnum.BUSINESSFLAG_8.getNo());
							break;
						case "公积金":
							housingFundType = HousingFundTypeEnum.FUNDTYPE_11.getNo();
							haveReceiveRefundDto.setBusinessFlag(negativeFlag ? BusinessFlagEnum.BUSINESSFLAG_9.getNo() : BusinessFlagEnum.BUSINESSFLAG_14.getNo());
							break;
						case "楼款":
							housingFundType = HousingFundTypeEnum.FUNDTYPE_5.getNo();
							haveReceiveRefundDto.setBusinessFlag(negativeFlag ? BusinessFlagEnum.BUSINESSFLAG_9.getNo() : BusinessFlagEnum.BUSINESSFLAG_14.getNo());
							break;
						case "首期":
							switch (haveReceiveRefundDto.getPaymentMethod()) {
								case "1":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_5.getNo();
									break;
								case "3":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_6.getNo();
									break;
								case "4":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_10.getNo();
									break;
								case "5":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_17.getNo();
									break;
								case "6":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_43.getNo();
									break;
								case "7":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_35.getNo();
									break;
								case "8":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_35.getNo();
									break;
							}
							haveReceiveRefundDto.setBusinessFlag(negativeFlag ? BusinessFlagEnum.BUSINESSFLAG_9.getNo() : BusinessFlagEnum.BUSINESSFLAG_14.getNo());
							break;
						case "挞定款":
							housingFundType = HousingFundTypeEnum.FUNDTYPE_3.getNo();
							haveReceiveRefundDto.setBusinessFlag(negativeFlag ? BusinessFlagEnum.BUSINESSFLAG_5.getNo() : BusinessFlagEnum.BUSINESSFLAG_8.getNo());
							break;
						case "现金折扣":
							housingFundType = HousingFundTypeEnum.FUNDTYPE_44.getNo();
							haveReceiveRefundDto.setBusinessFlag(negativeFlag ? BusinessFlagEnum.BUSINESSFLAG_9.getNo() : BusinessFlagEnum.BUSINESSFLAG_14.getNo());
							break;
						case "银行按揭":
							switch (haveReceiveRefundDto.getPaymentMethod()) {
								case "1":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_7.getNo();
									break;
								case "3":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_7.getNo();
									break;
								case "4":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_11.getNo();
									break;
								case "5":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_18.getNo();
									break;
								case "8":
									housingFundType = HousingFundTypeEnum.FUNDTYPE_36.getNo();
									break;
							}
							haveReceiveRefundDto.setBusinessFlag(negativeFlag ? BusinessFlagEnum.BUSINESSFLAG_9.getNo() : BusinessFlagEnum.BUSINESSFLAG_14.getNo());
							break;
					}
					if (negativeFlag) {
						List<String> fundTypeList = Arrays.asList("3", "5");
						String fundType = housingFundType;
						if (fundTypeList.contains(housingFundType)) {
							switch (haveReceiveRefundDto.getPaymentMethod()) {
								case "1":
									fundType = HousingFundTypeEnum.FUNDTYPE_5.getNo();
									break;
								case "3":
									fundType = HousingFundTypeEnum.FUNDTYPE_6.getNo();
									break;
								case "4":
									fundType = HousingFundTypeEnum.FUNDTYPE_10.getNo();
									break;
								case "5":
									fundType = HousingFundTypeEnum.FUNDTYPE_17.getNo();
									break;
								case "6":
									fundType = HousingFundTypeEnum.FUNDTYPE_43.getNo();
									break;
								case "7":
									fundType = HousingFundTypeEnum.FUNDTYPE_35.getNo();
									break;
								case "8":
									fundType = HousingFundTypeEnum.FUNDTYPE_35.getNo();
									break;
							}
						} else if ("11".equals(housingFundType)) {
							switch (haveReceiveRefundDto.getPaymentMethod()) {
								case "4":
									fundType = HousingFundTypeEnum.FUNDTYPE_11.getNo();
									break;
								case "5":
									fundType = HousingFundTypeEnum.FUNDTYPE_18.getNo();
									break;
							}
						}
						for (NeedReceiveRefund receiveRefund : needReceiveRefundResponseList.getEntity()) {
							if (receiveRefund.getHousingFundType().equals(fundType)) {
								haveReceiveRefundDto.setReceivablesId(receiveRefund.getId());
								break;
							}
						}
					} else {
						NeedReceiveRefundDto needReceiveRefundDto = new NeedReceiveRefundDto();
						needReceiveRefundDto.setRelationId(signedResponseList.getEntity() == null ? subscribe.getId() : signedResponseList.getEntity().get(0).getId());
						needReceiveRefundDto.setBusinessType(BusinessTypeEnum.SIGNED.getNo());
						needReceiveRefundDto.setReceivablesDate(haveReceiveRefundDto.getStatementDate());
						needReceiveRefundDto.setTotalReceivablesAmount(haveReceiveRefundDto.getRealAmount());
						needReceiveRefundDto.setCustomerId(signedResponseList.getEntity() == null ? subscribe.getCustomerId() : signedResponseList.getEntity().get(0).getCustomerId());
						needReceiveRefundDto.setRoomId(signedResponseList.getEntity() == null ? subscribe.getRoomId() : signedResponseList.getEntity().get(0).getRoomId());
						needReceiveRefundDto.setHousingFundType(housingFundType);
						needReceiveRefundDto.setBusinessFlag(BusinessFlagEnum.BUSINESSFLAG_9.getNo());
						needReceiveRefundDto.setStatus("1");
						needReceiveRefundDto.setPostponedStatus("0");
						needReceiveRefundDto.setDocType("1");
						needReceiveRefundDto.setMerchantId(signedResponseList.getEntity() == null ? subscribe.getMerchantId() : signedResponseList.getEntity().get(0).getMerchantId());
						needReceiveRefundDto.setAccessCode(signedResponseList.getEntity() == null ? subscribe.getAccessCode() : signedResponseList.getEntity().get(0).getAccessCode());
						needReceiveRefundDto.setUserCode(signedResponseList.getEntity() == null ? subscribe.getUserCode() : signedResponseList.getEntity().get(0).getUserCode());
						needReceiveRefundDto.setCreateUserId(1L);
						needReceiveRefundDto.setCreateTime(new Date());
						needReceiveRefundDto.setReceivablesFunds(HousingFundTypeEnum.getByNo(housingFundType).getDes());
						needReceiveRefundDto.setPaymentMethod(haveReceiveRefundDto.getPaymentMethod());
						needReceiveRefundDto.setRemark("实收数据导入1013_" + indexNo);
						needReceiveRefundDto.setDelFlag(DelFlagEnum.UNDEL.getNo());

						Response<NeedReceiveRefundDto> needReceiveRefundDtoResponse = needReceiveRefundServiceFacade.save(needReceiveRefundDto);
						haveReceiveRefundDto.setReceivablesId(needReceiveRefundDtoResponse.getEntity().getId());
					}
					haveReceiveRefundDto.setHousingFundType(housingFundType);
					// 绑定认购
				}else{
					NeedReceiveRefundDto needReceiveRefundDto = new NeedReceiveRefundDto();
					needReceiveRefundDto.setRelationId(subscribe.getId());
					needReceiveRefundDto.setBusinessType(BusinessTypeEnum.SUBSCRIBE.getNo());
					needReceiveRefundDto.setReceivablesDate(haveReceiveRefundDto.getStatementDate());
					needReceiveRefundDto.setTotalReceivablesAmount(haveReceiveRefundDto.getRealAmount());
					needReceiveRefundDto.setCustomerId(subscribe.getCustomerId());
					needReceiveRefundDto.setRoomId(subscribe.getRoomId());
					needReceiveRefundDto.setHousingFundType(negativeFlag?HousingFundTypeEnum.FUNDTYPE_3.getNo():HousingFundTypeEnum.FUNDTYPE_4.getNo());
					needReceiveRefundDto.setBusinessFlag(negativeFlag?BusinessFlagEnum.BUSINESSFLAG_5.getNo():BusinessFlagEnum.BUSINESSFLAG_8.getNo());
					needReceiveRefundDto.setStatus("0");
					needReceiveRefundDto.setPostponedStatus("0");
					needReceiveRefundDto.setDocType(negativeFlag?"0":"1");
					needReceiveRefundDto.setMerchantId(subscribe.getMerchantId());
					needReceiveRefundDto.setAccessCode(subscribe.getAccessCode());
					needReceiveRefundDto.setUserCode(subscribe.getUserCode());
					needReceiveRefundDto.setCreateUserId(1L);
					needReceiveRefundDto.setCreateTime(new Date());
					needReceiveRefundDto.setReceivablesFunds(negativeFlag?BusinessFlagEnum.BUSINESSFLAG_5.getDes():BusinessFlagEnum.BUSINESSFLAG_8.getDes());
					needReceiveRefundDto.setPaymentMethod(haveReceiveRefundDto.getPaymentMethod());
					needReceiveRefundDto.setRemark("实收数据导入1015_" + indexNo);
					needReceiveRefundDto.setDelFlag(DelFlagEnum.UNDEL.getNo());

					Response<NeedReceiveRefundDto> needReceiveRefundDtoResponse = needReceiveRefundServiceFacade.save(needReceiveRefundDto);
					haveReceiveRefundDto.setReceivablesId(needReceiveRefundDtoResponse.getEntity().getId());
					haveReceiveRefundDto.setBusinessFlag(negativeFlag ? BusinessFlagEnum.BUSINESSFLAG_5.getNo() : BusinessFlagEnum.BUSINESSFLAG_8.getNo());
					haveReceiveRefundDto.setHousingFundType(needReceiveRefundDtoResponse.getEntity().getHousingFundType());
				}

				haveReceiveRefundDto.setTransferStatus("0");
				haveReceiveRefundDto.setDelFlag(DelFlagEnum.UNDEL.getNo());
				haveReceiveRefundDto.setCreateUserId(1L);
				haveReceiveRefundDto.setCreateTime(new Date());
				haveReceiveRefundDto.setRemark("实收数据导入1015_"+indexNo);

				subscribeServiceFacade.addHaveReceiveRefundImport(haveReceiveRefundDto);
			}catch (BusinessException be){
				System.out.println("BusinessException: "+indexNo);
			}catch (Exception e){
				System.out.println(indexNo);
			}
		}
	}

	public void importReceiptData3(FastExcel fastExcel) throws Exception
	{
		for(int i=6; i<fastExcel.getWorkbook().getSheetAt(0).getLastRowNum(); i++) {
			Row row = fastExcel.getWorkbook().getSheetAt(0).getRow(i);
			String indexNo = (String) FastExcel.getCellValue(row.getCell(0));
			try {
				String paymentMethod = (String) FastExcel.getCellValue(row.getCell(21));
				if ("预约金".equals(paymentMethod) || "垫付首期".equals(paymentMethod)) {
					continue;
				}

				String projectName = (String) FastExcel.getCellValue(row.getCell(1));
				String buildingName = (String) FastExcel.getCellValue(row.getCell(2));
				String roomName = (String) FastExcel.getCellValue(row.getCell(4));
				roomName = (projectName+"-"+buildingName+"-"+roomName).replaceAll("-","-->");
				ProjectRoomDto projectRoomDto = new ProjectRoomDto();
				projectRoomDto.setCombinedName(roomName);
				ResponseList<ProjectRoom> roomResponseList = projectRoomServiceFacade.findByDto(projectRoomDto);
				ProjectRoom projectRoom = roomResponseList.getEntity().get(0);

				HaveReceiveRefundDto haveReceiveRefundDto = new HaveReceiveRefundDto();
				String statementDate = (String) FastExcel.getCellValue(row.getCell(13));
				haveReceiveRefundDto.setStatementDate(DateUtils.StringToDate(statementDate, DateUtils.parsePatterns[1]));
				String payerName = (String) FastExcel.getCellValue(row.getCell(17));
				haveReceiveRefundDto.setPayerName(payerName);
				haveReceiveRefundDto.setRoomId(projectRoom.getId());
				BigDecimal realAmount = new BigDecimal((String) FastExcel.getCellValue(row.getCell(22)));
				boolean negativeFlag = realAmount.compareTo(new BigDecimal(0))==1;
				haveReceiveRefundDto.setDocType(negativeFlag?"0":"1");
				haveReceiveRefundDto.setRealAmount(new BigDecimal(Math.abs(realAmount.doubleValue())));

				ResponseList<HaveReceiveRefund> haveReceiveRefundResponseList = haveReceiveRefundServiceFacade.findByDto(haveReceiveRefundDto);
				if(haveReceiveRefundResponseList.getEntity()!=null){
					for(HaveReceiveRefund haveReceiveRefund : haveReceiveRefundResponseList.getEntity()) {
						HaveReceiveRefundDto updateHaveReceiveRefund = new HaveReceiveRefundDto();
						updateHaveReceiveRefund.setId(haveReceiveRefund.getId());
						String docTypeName = (String) FastExcel.getCellValue(row.getCell(14));
						updateHaveReceiveRefund.setDocTypeName(docTypeName);
						String businessTypeName = (String) FastExcel.getCellValue(row.getCell(19));
						updateHaveReceiveRefund.setBusinessTypeName(businessTypeName);
						String grayType = (String) FastExcel.getCellValue(row.getCell(20));
						updateHaveReceiveRefund.setGrayType(grayType);
						String gratName = (String) FastExcel.getCellValue(row.getCell(21));
						updateHaveReceiveRefund.setGratName(gratName);

						haveReceiveRefundServiceFacade.updateByPrimaryKey(updateHaveReceiveRefund, true);
					}
				}else{
					throw new BusinessException("", "");
				}

			} catch (BusinessException be) {
				System.out.println("BusinessException: " + indexNo);
			} catch (Exception e) {
				System.out.println(indexNo);
			}
		}
	}

	public void importReceiptData4(FastExcel fastExcel) throws Exception
	{
		for(int i=6; i<fastExcel.getWorkbook().getSheetAt(0).getLastRowNum(); i++) {
			Row row = fastExcel.getWorkbook().getSheetAt(0).getRow(i);
			String indexNo = (String) FastExcel.getCellValue(row.getCell(0));
			try {
				String paymentMethod = (String) FastExcel.getCellValue(row.getCell(21));
				if (!"垫付首期".equals(paymentMethod)) {
					continue;
				}

				String projectName = (String) FastExcel.getCellValue(row.getCell(1));
				String buildingName = (String) FastExcel.getCellValue(row.getCell(2));
				String roomName = (String) FastExcel.getCellValue(row.getCell(4));
				roomName = (projectName+"-"+buildingName+"-"+roomName).replaceAll("-","-->");
				ProjectRoomDto projectRoomDto = new ProjectRoomDto();
				projectRoomDto.setCombinedName(roomName);
				ResponseList<ProjectRoom> roomResponseList = projectRoomServiceFacade.findByDto(projectRoomDto);
				ProjectRoom projectRoom = roomResponseList.getEntity().get(0);

				// 客户查询
				String customerName = ((String) FastExcel.getCellValue(row.getCell(18))).trim();
				if(customerName.indexOf(";")>-1){
					customerName = customerName.split(";")[0];
				}
				CustomerDto customerDto = new CustomerDto();
				customerDto.setCustomerName(customerName);
				if("陈丽".equals(customerName)){
					customerDto.setPeDocumentNumber("430921198406116167");
				}
				ResponseList<Customer> customerResponseList = customerServiceFacade.findByDto(customerDto);
				Customer customer = customerResponseList.getEntity().get(0);

				// 签约查询
				SignedDto signedDto = new SignedDto();
				signedDto.setRoomId(projectRoom.getId());
				signedDto.setCustomerId(customer.getId());
				ResponseList<Signed> signedResponseList = signedServiceFacade.findByDto(signedDto);
				if(signedResponseList.getEntity()==null){
					throw new BusinessException("", "");
				}
				Signed signed = signedResponseList.getEntity().get(0);

				String statementDate = (String) FastExcel.getCellValue(row.getCell(13));
				BigDecimal realAmount = new BigDecimal((String) FastExcel.getCellValue(row.getCell(22)));
				boolean negativeFlag = realAmount.compareTo(new BigDecimal(0))==1;
				String payerName = (String) FastExcel.getCellValue(row.getCell(17));
				String billType = (String) FastExcel.getCellValue(row.getCell(15));
				billType = "收据".equals(billType)?"2":"1";
				String bankName = (String) FastExcel.getCellValue(row.getCell(24));
				String bankCode = StringUtils.isBlank(bankName)?"":BankNameEnum.getByDescription(bankName).getNo();
				String billCode = (String) FastExcel.getCellValue(row.getCell(16));
				String statementType = (String) FastExcel.getCellValue(row.getCell(23));
				switch (statementType){
					case "POS机":
						statementType = StatementTypeEnum.POS_TRANSFER.getNo();
						break;
					case "现金":
						statementType = StatementTypeEnum.CASH.getNo();
						break;
					case "折扣":
						statementType = StatementTypeEnum.DISCOUNT.getNo();
						break;
					case "转账":
						statementType = StatementTypeEnum.TRANSFER.getNo();
						break;
				}
				String docTypeName = (String) FastExcel.getCellValue(row.getCell(14));
				String businessTypeName = (String) FastExcel.getCellValue(row.getCell(19));
				String grayType = (String) FastExcel.getCellValue(row.getCell(20));
				String gratName = (String) FastExcel.getCellValue(row.getCell(21));

				NeedAdvanceRefundDto needAdvanceRefundDto = new NeedAdvanceRefundDto();
				needAdvanceRefundDto.setRelationId(signed.getId());
				needAdvanceRefundDto.setBusinessType(BusinessTypeEnum.SIGNED.getNo());
				if(StringUtils.isNotBlank(statementDate)) {
					needAdvanceRefundDto.setReceivablesDate(DateUtils.stringToDate(statementDate, DateUtils.parsePatterns[1]));
				}
				needAdvanceRefundDto.setTotalReceivablesAmount(new BigDecimal(Math.abs(realAmount.doubleValue())));
				needAdvanceRefundDto.setCustomerId(signed.getCustomerId());
				needAdvanceRefundDto.setRoomId(signed.getRoomId());
				needAdvanceRefundDto.setHousingFundType(HousingFundTypeEnum.FUNDTYPE_45.getNo());
				needAdvanceRefundDto.setBusinessFlag(BusinessFlagEnum.BUSINESSFLAG_9.getNo());
				needAdvanceRefundDto.setStatus("0");
				needAdvanceRefundDto.setPostponedStatus("0");
				needAdvanceRefundDto.setDocType(negativeFlag?"0":"1");
				needAdvanceRefundDto.setMerchantId(signed.getMerchantId());
				needAdvanceRefundDto.setAccessCode(signed.getAccessCode());
				needAdvanceRefundDto.setUserCode(signed.getUserCode());
				needAdvanceRefundDto.setCreateUserId(1L);
				needAdvanceRefundDto.setCreateTime(new Date());
				needAdvanceRefundDto.setReceivablesFunds(HousingFundTypeEnum.FUNDTYPE_45.getDes());
				needAdvanceRefundDto.setPaymentMethod(signed.getPaymentMethod());
				needAdvanceRefundDto.setRemark("实收数据导入1028_"+indexNo);
				needAdvanceRefundDto.setDelFlag(DelFlagEnum.UNDEL.getNo());

				Response<NeedAdvanceRefundDto> advanceRefundResponse = needAdvanceRefundServiceFacade.save(needAdvanceRefundDto);

				HaveAdvanceRefundDto haveAdvanceRefundDto = new HaveAdvanceRefundDto();
				haveAdvanceRefundDto.setRelationId(signed.getId());
				haveAdvanceRefundDto.setReceivablesId(advanceRefundResponse.getEntity().getId());
				haveAdvanceRefundDto.setReceivablesType(ReceivablesTypeEnum.SIGNED.getNo());
				haveAdvanceRefundDto.setHousingFundType(HousingFundTypeEnum.FUNDTYPE_45.getNo());
				haveAdvanceRefundDto.setBusinessFlag(BusinessFlagEnum.BUSINESSFLAG_9.getNo());
				haveAdvanceRefundDto.setReceivablesFunds(HousingFundTypeEnum.FUNDTYPE_45.getDes());
				haveAdvanceRefundDto.setPaymentMethod(signed.getPaymentMethod());
				haveAdvanceRefundDto.setPayerName(payerName);
				haveAdvanceRefundDto.setCustomerId(signed.getCustomerId());
				haveAdvanceRefundDto.setRoomId(signed.getRoomId());
				haveAdvanceRefundDto.setBillType(billType);
				haveAdvanceRefundDto.setBankCode(bankCode);
				haveAdvanceRefundDto.setDocType(negativeFlag?"0":"1");
				haveAdvanceRefundDto.setRealAmount(new BigDecimal(Math.abs(realAmount.doubleValue())));
				haveAdvanceRefundDto.setBillCode(billCode);
				haveAdvanceRefundDto.setDeductibleAmount(BigDecimal.ZERO);
				haveAdvanceRefundDto.setStatementType(Long.parseLong(statementType));
				haveAdvanceRefundDto.setStatementDate(DateUtils.StringToDate(statementDate, DateUtils.parsePatterns[1]));
				haveAdvanceRefundDto.setTransferStatus("0");
				haveAdvanceRefundDto.setDocTypeName(docTypeName);
				haveAdvanceRefundDto.setBusinessTypeName(businessTypeName);
				haveAdvanceRefundDto.setGrayType(grayType);
				haveAdvanceRefundDto.setGratName(gratName);
				haveAdvanceRefundDto.setMerchantId(signed.getMerchantId());
				haveAdvanceRefundDto.setAccessCode(signed.getAccessCode());
				haveAdvanceRefundDto.setUserCode(signed.getUserCode());
				haveAdvanceRefundDto.setCreateUserId(1L);
				haveAdvanceRefundDto.setCreateTime(new Date());
				haveAdvanceRefundDto.setRemark("实收数据导入1028_"+indexNo);
				haveAdvanceRefundDto.setDelFlag(DelFlagEnum.UNDEL.getNo());

				haveAdvanceRefundServiceFacade.save(haveAdvanceRefundDto);

//				break;
			} catch (BusinessException be) {
				System.out.println("BusinessException: " + indexNo);
			} catch (Exception e) {
				System.out.println(indexNo);
			}
		}
	}

	public void importReceiptData5(FastExcel fastExcel) throws Exception
	{
		for(int i=6; i<fastExcel.getWorkbook().getSheetAt(0).getLastRowNum(); i++) {
			Row row = fastExcel.getWorkbook().getSheetAt(0).getRow(i);
			String indexNo = (String) FastExcel.getCellValue(row.getCell(0));
			try {
				String paymentMethod = (String) FastExcel.getCellValue(row.getCell(22));
				if (!"垫付首期".equals(paymentMethod)) {
					continue;
				}

				String projectName = (String) FastExcel.getCellValue(row.getCell(2));
				String buildingName = (String) FastExcel.getCellValue(row.getCell(3));
				String roomName = (String) FastExcel.getCellValue(row.getCell(5));
				roomName = (projectName+"-"+buildingName+"-"+roomName).replaceAll("-","-->");
				ProjectRoomDto projectRoomDto = new ProjectRoomDto();
				projectRoomDto.setCombinedName(roomName);
				ResponseList<ProjectRoom> roomResponseList = projectRoomServiceFacade.findByDto(projectRoomDto);
				ProjectRoom projectRoom = roomResponseList.getEntity().get(0);

				// 客户查询
				String customerName = ((String) FastExcel.getCellValue(row.getCell(19))).trim();
				if(customerName.indexOf(";")>-1){
					customerName = customerName.split(";")[0];
				}
				CustomerDto customerDto = new CustomerDto();
				customerDto.setCustomerName(customerName);
				if("陈丽".equals(customerName)){
					customerDto.setPeDocumentNumber("430921198406116167");
				}
				ResponseList<Customer> customerResponseList = customerServiceFacade.findByDto(customerDto);
				Customer customer = customerResponseList.getEntity().get(0);

				// 签约查询
				SignedDto signedDto = new SignedDto();
				signedDto.setRoomId(projectRoom.getId());
				signedDto.setCustomerId(customer.getId());
				ResponseList<Signed> signedResponseList = signedServiceFacade.findByDto(signedDto);
				if(signedResponseList.getEntity()==null){
					throw new BusinessException("", "");
				}
				Signed signed = signedResponseList.getEntity().get(0);

				String statementDate = (String) FastExcel.getCellValue(row.getCell(14));
				BigDecimal realAmount = new BigDecimal((String) FastExcel.getCellValue(row.getCell(23)));
				boolean negativeFlag = realAmount.compareTo(new BigDecimal(0))==1;
				String payerName = (String) FastExcel.getCellValue(row.getCell(18));
				String billType = (String) FastExcel.getCellValue(row.getCell(16));
				billType = "收据".equals(billType)?"2":"1";
				String bankName = (String) FastExcel.getCellValue(row.getCell(25));
				String bankCode = StringUtils.isBlank(bankName)?"":BankNameEnum.getByDescription(bankName).getNo();
				String billCode = (String) FastExcel.getCellValue(row.getCell(17));
				String statementType = (String) FastExcel.getCellValue(row.getCell(24));
				switch (statementType){
					case "POS机":
						statementType = StatementTypeEnum.POS_TRANSFER.getNo();
						break;
					case "现金":
						statementType = StatementTypeEnum.CASH.getNo();
						break;
					case "折扣":
						statementType = StatementTypeEnum.DISCOUNT.getNo();
						break;
					case "转账":
						statementType = StatementTypeEnum.TRANSFER.getNo();
						break;
				}
				String docTypeName = (String) FastExcel.getCellValue(row.getCell(15));
				String businessTypeName = (String) FastExcel.getCellValue(row.getCell(20));
				String grayType = (String) FastExcel.getCellValue(row.getCell(21));
				String gratName = (String) FastExcel.getCellValue(row.getCell(22));

				NeedAdvanceRefundDto needAdvanceRefundDto = new NeedAdvanceRefundDto();
				needAdvanceRefundDto.setRelationId(signed.getId());
				needAdvanceRefundDto.setBusinessType(BusinessTypeEnum.SIGNED.getNo());
				if(StringUtils.isNotBlank(statementDate)) {
					needAdvanceRefundDto.setReceivablesDate(DateUtils.stringToDate(statementDate, DateUtils.parsePatterns[1]));
				}
				needAdvanceRefundDto.setTotalReceivablesAmount(new BigDecimal(Math.abs(realAmount.doubleValue())));
				needAdvanceRefundDto.setCustomerId(signed.getCustomerId());
				needAdvanceRefundDto.setRoomId(signed.getRoomId());
				needAdvanceRefundDto.setHousingFundType(HousingFundTypeEnum.FUNDTYPE_45.getNo());
				needAdvanceRefundDto.setBusinessFlag(BusinessFlagEnum.BUSINESSFLAG_9.getNo());
				needAdvanceRefundDto.setStatus("0");
				needAdvanceRefundDto.setPostponedStatus("0");
				needAdvanceRefundDto.setDocType(negativeFlag?"0":"1");
				needAdvanceRefundDto.setMerchantId(signed.getMerchantId());
				needAdvanceRefundDto.setAccessCode(signed.getAccessCode());
				needAdvanceRefundDto.setUserCode(signed.getUserCode());
				needAdvanceRefundDto.setCreateUserId(1L);
				needAdvanceRefundDto.setCreateTime(new Date());
				needAdvanceRefundDto.setReceivablesFunds(HousingFundTypeEnum.FUNDTYPE_45.getDes());
				needAdvanceRefundDto.setPaymentMethod(signed.getPaymentMethod());
				needAdvanceRefundDto.setRemark("实收数据导入1029_"+indexNo);
				needAdvanceRefundDto.setDelFlag(DelFlagEnum.UNDEL.getNo());

				Response<NeedAdvanceRefundDto> advanceRefundResponse = needAdvanceRefundServiceFacade.save(needAdvanceRefundDto);

				HaveAdvanceRefundDto haveAdvanceRefundDto = new HaveAdvanceRefundDto();
				haveAdvanceRefundDto.setRelationId(signed.getId());
				haveAdvanceRefundDto.setReceivablesId(advanceRefundResponse.getEntity().getId());
				haveAdvanceRefundDto.setReceivablesType(ReceivablesTypeEnum.SIGNED.getNo());
				haveAdvanceRefundDto.setHousingFundType(HousingFundTypeEnum.FUNDTYPE_45.getNo());
				haveAdvanceRefundDto.setBusinessFlag(BusinessFlagEnum.BUSINESSFLAG_9.getNo());
				haveAdvanceRefundDto.setReceivablesFunds(HousingFundTypeEnum.FUNDTYPE_45.getDes());
				haveAdvanceRefundDto.setPaymentMethod(signed.getPaymentMethod());
				haveAdvanceRefundDto.setPayerName(payerName);
				haveAdvanceRefundDto.setCustomerId(signed.getCustomerId());
				haveAdvanceRefundDto.setRoomId(signed.getRoomId());
				haveAdvanceRefundDto.setBillType(billType);
				haveAdvanceRefundDto.setBankCode(bankCode);
				haveAdvanceRefundDto.setDocType(negativeFlag?"0":"1");
				haveAdvanceRefundDto.setRealAmount(new BigDecimal(Math.abs(realAmount.doubleValue())));
				haveAdvanceRefundDto.setBillCode(billCode);
				haveAdvanceRefundDto.setDeductibleAmount(BigDecimal.ZERO);
				haveAdvanceRefundDto.setStatementType(Long.parseLong(statementType));
				haveAdvanceRefundDto.setStatementDate(DateUtils.StringToDate(statementDate, DateUtils.parsePatterns[1]));
				haveAdvanceRefundDto.setTransferStatus("0");
				haveAdvanceRefundDto.setDocTypeName(docTypeName);
				haveAdvanceRefundDto.setBusinessTypeName(businessTypeName);
				haveAdvanceRefundDto.setGrayType(grayType);
				haveAdvanceRefundDto.setGratName(gratName);
				haveAdvanceRefundDto.setMerchantId(signed.getMerchantId());
				haveAdvanceRefundDto.setAccessCode(signed.getAccessCode());
				haveAdvanceRefundDto.setUserCode(signed.getUserCode());
				haveAdvanceRefundDto.setCreateUserId(1L);
				haveAdvanceRefundDto.setCreateTime(new Date());
				haveAdvanceRefundDto.setRemark("实收数据导入1029_"+indexNo);
				haveAdvanceRefundDto.setDelFlag(DelFlagEnum.UNDEL.getNo());

				haveAdvanceRefundServiceFacade.save(haveAdvanceRefundDto);

//				break;
			} catch (BusinessException be) {
				System.out.println("BusinessException: " + indexNo);
			} catch (Exception e) {
				System.out.println(indexNo);
			}
		}
	}

	public void importSignedData(FastExcel fastExcel) throws Exception
	{
		for(int i=1; i<fastExcel.getWorkbook().getSheetAt(0).getLastRowNum(); i++){
			Row row = fastExcel.getWorkbook().getSheetAt(0).getRow(i);
			String indexNo = (String) FastExcel.getCellValue(row.getCell(0));
			try {
			AddSignedReqDto addSignedReqDto = new AddSignedReqDto();

			addSignedReqDto.setSubscribeAmount((String) FastExcel.getCellValue(row.getCell(0)));
			addSignedReqDto.setSignedCode((String) FastExcel.getCellValue(row.getCell(2)));

			String roomName = ((String) FastExcel.getCellValue(row.getCell(5))).replaceAll("-1-", "-").replaceAll("-","-->");
			ProjectRoomDto projectRoomDto = new ProjectRoomDto();
			projectRoomDto.setCombinedName(roomName);
			ResponseList<ProjectRoom> roomResponseList = projectRoomServiceFacade.findByDto(projectRoomDto);
			ProjectRoom projectRoom = roomResponseList.getEntity().get(0);

            String[] customerNameArray = ((String) FastExcel.getCellValue(row.getCell(7))).split(";");
            String[] documentNumberArray = ((String) FastExcel.getCellValue(row.getCell(8))).split(";");
            String[] mobileArray = ((String) FastExcel.getCellValue(row.getCell(9))).split(";");
            CustomerDto customerDto = new CustomerDto();
            customerDto.setCustomerName(customerNameArray[0]);
            customerDto.setPeDocumentNumber(documentNumberArray[0]);
            ResponseList<Customer> customerResponseList = customerServiceFacade.findByDto(customerDto);
            addSignedReqDto.setCustomerId(customerResponseList.getEntity().get(0).getId() + "");

            if(customerNameArray.length>1){
                List<CustomerFamilyListReq> familyListReqList = new ArrayList<>();
                for(int j=1; j<customerNameArray.length; j++) {
                    CustomerFamilyListReq familyListReq = new CustomerFamilyListReq();
                    familyListReq.setMemberName(customerNameArray[j]);
                    familyListReq.setDocumentType("1");
                    familyListReq.setDocumentNumber(documentNumberArray[j]);
                    familyListReq.setMobile(mobileArray[j]);
                    familyListReqList.add(familyListReq);
                }
                addSignedReqDto.setCustomerFamilyList(familyListReqList);
            }

			SubscribeDto subscribeDto = new SubscribeDto();
			subscribeDto.setRoomId(projectRoom.getId());
            subscribeDto.setCustomerId(Long.parseLong(addSignedReqDto.getCustomerId()));
			subscribeDto.setBusinessStatus("1");
			ResponseList<Subscribe> subscribeResponseList = subscribeServiceFacade.findByDto(subscribeDto);
			Subscribe subscribe = subscribeResponseList.getEntity().get(0);

			addSignedReqDto.setSubscribeId(subscribe.getId()+"");
			addSignedReqDto.setSubscribeCode(subscribe.getSubscribeCode());
			addSignedReqDto.setProjectId(projectRoom.getProjectId());
			addSignedReqDto.setProjectCode(projectRoom.getProjectCode());

			addSignedReqDto.setSignedHouses(projectRoom.getId()+"");
			addSignedReqDto.setCalculateArea((String) FastExcel.getCellValue(row.getCell(11)));

			addSignedReqDto.setDiscountPrice((String) FastExcel.getCellValue(row.getCell(12)));
			addSignedReqDto.setDiscountTotalPrice((String) FastExcel.getCellValue(row.getCell(13)));
			String cell15_value = (String) FastExcel.getCellValue(row.getCell(15));
			addSignedReqDto.setDiscountAmount(new BigDecimal(addSignedReqDto.getDiscountTotalPrice()).subtract(new BigDecimal(cell15_value)).toString());
			addSignedReqDto.setDiscountRate((String) FastExcel.getCellValue(row.getCell(18)));
			addSignedReqDto.setDiscountNote((String) FastExcel.getCellValue(row.getCell(19)));

			addSignedReqDto.setContractPrice((String) FastExcel.getCellValue(row.getCell(14)));
			addSignedReqDto.setContractAmount((String) FastExcel.getCellValue(row.getCell(15)));
			addSignedReqDto.setDiscountAfterAmount(addSignedReqDto.getContractAmount());
			addSignedReqDto.setDealCategory("2");

			String paymentMethod = (String) FastExcel.getCellValue(row.getCell(16));
			addSignedReqDto.setDecorationPaymentMethod(paymentMethod.indexOf("首付分期")>-1?"2":"");

			switch (paymentMethod.trim()){
				case "车位分期":
					addSignedReqDto.setPaymentMethod(PaymentMethodEnum.CAR_INSTALLMENT_PAYMENT.getNo());
					break;
				case "车位一次性付款":
					addSignedReqDto.setPaymentMethod(PaymentMethodEnum.CAR_LUMP_SUM.getNo());
					break;
				case "车位银行按揭":
					addSignedReqDto.setPaymentMethod(PaymentMethodEnum.CAR_COMMERCIAL_MORTGAGE.getNo());
					break;
				case "公积金":
					addSignedReqDto.setPaymentMethod(PaymentMethodEnum.PROVIDENT_FUND_MORTGAGE.getNo());
					break;
				case "首付分期":
					addSignedReqDto.setPaymentMethod(PaymentMethodEnum.LUMP_SUM.getNo());
					break;
				case "首付分期【公积金】":
					addSignedReqDto.setPaymentMethod(PaymentMethodEnum.PROVIDENT_FUND_MORTGAGE.getNo());
					break;
				case "首付分期【银行按揭】":
					addSignedReqDto.setPaymentMethod(PaymentMethodEnum.COMMERCIAL_MORTGAGE.getNo());
					break;
				case "首付分期【组合贷】":
					addSignedReqDto.setPaymentMethod(PaymentMethodEnum.COMBINED_LOAN.getNo());
					break;
				case "一次性付款":
					addSignedReqDto.setPaymentMethod(PaymentMethodEnum.LUMP_SUM.getNo());
					break;
				case "银行按揭":
					addSignedReqDto.setPaymentMethod(PaymentMethodEnum.COMMERCIAL_MORTGAGE.getNo());
					break;
				case "组合贷":
					addSignedReqDto.setPaymentMethod(PaymentMethodEnum.COMBINED_LOAN.getNo());
					break;
			}

			UserDto userDto = new UserDto();
			userDto.setUserName((String) FastExcel.getCellValue(row.getCell(22)));
			ResponseList<User> userResponseList = userServiceFacade.findByDto(userDto);
			if(userResponseList.getEntity()!=null){
				addSignedReqDto.setUserId(userResponseList.getEntity().get(0).getId()+"");
				addSignedReqDto.setAgentId(userResponseList.getEntity().get(0).getUserCode());
			}

			String contractDate = (String) FastExcel.getCellValue(row.getCell(1));
			String handoverDate = (String) FastExcel.getCellValue(row.getCell(4));
			String attributionDate = (String) FastExcel.getCellValue(row.getCell(24));
//			addSignedReqDto.setContractDate(DateUtils.StringToDate(contractDate, DateUtils.parsePatterns[1]));
//			addSignedReqDto.setHandoverDate(DateUtils.StringToDate(handoverDate, DateUtils.parsePatterns[1]));
//			addSignedReqDto.setAttributionDate(DateUtils.StringToDate(attributionDate, DateUtils.parsePatterns[1]));
			addSignedReqDto.setContractNo((String) FastExcel.getCellValue(row.getCell(2)));

			addSignedReqDto.setRemark("签约数据导入1012");

			subscribeServiceFacade.addSignedImport(addSignedReqDto);
			}catch (Exception e){
				System.out.println(indexNo);
			}
		}
	}

	public void importSubscribeData(FastExcel fastExcel) throws Exception
	{
		for(int i=1; i<fastExcel.getWorkbook().getSheetAt(0).getLastRowNum(); i++){
			try {

				Row row = fastExcel.getWorkbook().getSheetAt(0).getRow(i);
				AddSubscribeReqDto addSubscribeReqDto = new AddSubscribeReqDto();

				String[] customerNameArray = ((String) FastExcel.getCellValue(row.getCell(7))).split(";");
				String[] documentNumberArray = ((String) FastExcel.getCellValue(row.getCell(8))).split(";");
				String[] mobileArray = ((String) FastExcel.getCellValue(row.getCell(11))).split(";");
				CustomerDto customerDto = new CustomerDto();
				customerDto.setCustomerName(customerNameArray[0]);
				customerDto.setPeDocumentNumber(documentNumberArray[0]);
				ResponseList<Customer> customerResponseList = customerServiceFacade.findByDto(customerDto);
				addSubscribeReqDto.setCustomerId(customerResponseList.getEntity().get(0).getId() + "");
				addSubscribeReqDto.setSubscribeCode((String) FastExcel.getCellValue(row.getCell(0)));

				String roomName = ((String) FastExcel.getCellValue(row.getCell(2))).replaceAll("-1-", "-").replaceAll("-", "-->");
				ProjectRoomDto projectRoomDto = new ProjectRoomDto();
				projectRoomDto.setCombinedName(roomName);
				ResponseList<ProjectRoom> roomResponseList = projectRoomServiceFacade.findByDto(projectRoomDto);
				ProjectRoom projectRoom = roomResponseList.getEntity().get(0);
				addSubscribeReqDto.setRoomId(projectRoom.getId() + "");
				addSubscribeReqDto.setProjectId(projectRoom.getProjectId());
				addSubscribeReqDto.setProjectCode(projectRoom.getProjectCode());

				addSubscribeReqDto.setCalculateArea(projectRoom.getBillingArea().toString());

				addSubscribeReqDto.setDiscountPrice((String) FastExcel.getCellValue(row.getCell(15)));
				addSubscribeReqDto.setDiscountTotalPrice((String) FastExcel.getCellValue(row.getCell(16)));
				addSubscribeReqDto.setContractPrice((String) FastExcel.getCellValue(row.getCell(17)));
				String cell18_value = (String) FastExcel.getCellValue(row.getCell(18));
				addSubscribeReqDto.setContractAmount(cell18_value);
				addSubscribeReqDto.setDiscountAfterAmount(cell18_value);
				addSubscribeReqDto.setDiscountAmount(new BigDecimal(addSubscribeReqDto.getDiscountTotalPrice()).subtract(new BigDecimal(cell18_value)).toString());
				addSubscribeReqDto.setDiscountRate((String) FastExcel.getCellValue(row.getCell(21)));
				addSubscribeReqDto.setDiscountNote((String) FastExcel.getCellValue(row.getCell(22)));

				addSubscribeReqDto.setDepositReceivable(roomName.indexOf("车位") > -1 ? "20000.00" : "2000.00");
				addSubscribeReqDto.setDealCategory("2");

				UserDto userDto = new UserDto();
				userDto.setUserName((String) FastExcel.getCellValue(row.getCell(25)));
				ResponseList<User> userResponseList = userServiceFacade.findByDto(userDto);
				if (userResponseList.getEntity() != null) {
					addSubscribeReqDto.setAgentId(userResponseList.getEntity().get(0).getUserCode());
					addSubscribeReqDto.setUserId(userResponseList.getEntity().get(0).getId() + "");
				}

				addSubscribeReqDto.setSubscribeDate((String) FastExcel.getCellValue(row.getCell(1)));

				addSubscribeReqDto.setRemark("认购数据导入1012");

				if (customerNameArray.length > 1) {
					List<CustomerFamilyListReq> familyListReqList = new ArrayList<>();
					for (int j = 1; j < customerNameArray.length; j++) {
						CustomerFamilyListReq familyListReq = new CustomerFamilyListReq();
						familyListReq.setMemberName(customerNameArray[j]);
						familyListReq.setDocumentType("1");
						familyListReq.setDocumentNumber(documentNumberArray[j]);
						familyListReq.setMobile(mobileArray[j]);
						familyListReqList.add(familyListReq);
					}
					addSubscribeReqDto.setCustomerFamilyList(familyListReqList);
				}

				subscribeServiceFacade.addSubscribeImport(addSubscribeReqDto);
			}catch (Exception e){
				System.out.println(i);
			}
		}
	}

	//查找重复电话号码
	private Map<String,String> findRepeatPhone(List<CustomerModel> list)throws Exception{
		Map<String,String> phoneMap=new  HashMap<String,String>();
        Map<String,String> repeatPhonesMap=new HashMap<String,String>();
		for(int i = 0; i < list.size(); i++){
			CustomerModel customerModel =list.get(i);
			//电话1
			String customerPhone=customerModel.getCustomerPhone();
			//电话2
			String phone=customerModel.getPhone();
			if(StringUtils.isBlank(customerPhone) || StringUtils.isBlank(phone)){
				continue;
			}
			if(phoneMap.containsKey(customerPhone)){
				//电话1重复则放入重复的map，同时去重复
				repeatPhonesMap.put(customerPhone, customerPhone);
			}
			phoneMap.put(customerPhone, customerPhone);
			if(phoneMap.containsKey(phone)){
				//电话2重复则放入重复的map，同时去重复
				repeatPhonesMap.put(phone, phone);
			}
			phoneMap.put(phone, phone);
		}
		return repeatPhonesMap;
	}
	
	private CustomerImportVo addCustomer(List<CustomerModel> customerList,Map<String,String> repeatMap,User user,Long projectId){
		List<CustomerModel> errorList=new ArrayList<CustomerModel>();
		int addCount=0;
		for(CustomerModel cm : customerList	){
			cm.setMerchantId(user.getMerchantId());
			cm.setProjectId(projectId);
			Map<String,Object> resultMap=validateCustomerModel(cm,repeatMap);
			Boolean isSuccess=(Boolean)resultMap.get("isSuccess");
			if(!isSuccess){
				String error=(String)resultMap.get("error");
				cm.setErrorInfo(error);
				errorList.add(cm);
				continue;
			}
			CustomerDto customerDto=(CustomerDto)resultMap.get("customerDto");
			customerDto.setProjectId(projectId);

			customerDto.setUserId(user.getId());
			customerDto.setUserCode(user.getUserCode());
			customerDto.setMerchantId(user.getMerchantId());
			customerDto.setCreateUserId(user.getId());
			customerDto.setDelFlag("0");
			customerDto.setUpdateUserId(user.getId());
			customerDto.setUpdateTime(new Date());
			//获取经济人
//			UserDto userDto = new UserDto();
//			ResponseList<User> userList = userServiceFacade.findByDto(userDto);
//			if (userList.getEntity().size()>0){
//				customerDto.setUserCode(String.valueOf(userList.getEntity().get(0).getUserCode()));
//			}
			try {
				customerServiceFacade.allAddCustomer(customerDto);
				addCount++;
			} catch (Exception e) {
				logger.error("addCustomer error: ", e);
				cm.setErrorInfo(e.getMessage());
				errorList.add(cm);
			}
		}
		
		//保存错误信息
		String batchId=UUID.randomUUID().toString();
		if(errorList.size()>0){
			List<CustomerError> list=new ArrayList<CustomerError>();
			for(CustomerModel cm : errorList){
				String cmStr = JSON.toJSONString(cm);
				JSONObject object=JSONObject.parseObject(cmStr);
				CustomerError ce=new CustomerError();
				ce.setBatchId(batchId);
				ce.setCreateUserId(user.getId());
				ce.setMerchantId(user.getMerchantId());
				ce.setCreateTime(new Date());
				ce.setUpdateTime(new Date());
				ce.setDelFlag("0");
				ce.setRowData(object.toJSONString());
				list.add(ce);
			}
			customerErrorServiceFacade.insertByBatch(list);
		}
		CustomerImportVo ci=new CustomerImportVo();
		ci.setSuccessCount(addCount);
		ci.setErrorCount(errorList.size());
		ci.setBatchId(batchId);
		return ci;
	}
	
	private Map<String,Object> validateCustomerModel(CustomerModel cm,Map<String,String> repeatMap){
		Map<String,Object> resultMap=new HashMap<String,Object>();
		StringBuffer error=new StringBuffer("");
		//电话1
		String customerPhone=cm.getCustomerPhone();
		//电话2
		String phone=cm.getPhone();
		String zhengjianhaoma = cm.getZhengjianhaoma();
		String familyValueOneThree = cm.getFamilyValueOneThree();
		String familyValueTwoThree = cm.getFamilyValueTwoThree();
		String familyValueThreeThree = cm.getFamilyValueThreeThree();
		String familyValueFourThree = cm.getFamilyValueFourThree();
		boolean isSuccess=true;
		if(StringUtils.isNotBlank(customerPhone) && repeatMap.containsKey(customerPhone)){
			isSuccess=false;
			error.append("联系电话表内重复;");
		}
		if(StringUtils.isNotBlank(phone) && repeatMap.containsKey(phone)){
			isSuccess=false;
			error.append("手机号表内重复;");
		}
		if(StringUtils.isNotBlank(zhengjianhaoma)&&zhengjianhaoma.length()>18){
			isSuccess=false;
			error.append("证件号码不能超过18位");
		}
		if(StringUtils.isNotBlank(familyValueOneThree)&&familyValueOneThree.length()>18){
			isSuccess=false;
			error.append("证件号码不能超过18位");
		}
		if(StringUtils.isNotBlank(familyValueTwoThree)&&familyValueTwoThree.length()>18){
			isSuccess=false;
			error.append("证件号码不能超过18位");
		}
		if(StringUtils.isNotBlank(familyValueThreeThree)&&familyValueThreeThree.length()>18){
			isSuccess=false;
			error.append("证件号码不能超过18位");
		}
		if(StringUtils.isNotBlank(familyValueFourThree)&&familyValueFourThree.length()>18){
			isSuccess=false;
			error.append("证件号码不能超过18位");
		}
		Response<CustomerDto> response=customerExcelServiceFacade.validateCustomerModel(cm);
		//当表内重复验证通过，则继续判断其它规则是否通过
		if(isSuccess){
			isSuccess=response.isSuccess();
		}
		if(isSuccess){
			resultMap.put("isSuccess", isSuccess);
			resultMap.put("customerDto", response.getEntity());
		}else{
			if(!response.isSuccess()){
				error.append(response.getResultMessage());
			}
			resultMap.put("isSuccess", isSuccess);
			resultMap.put("error", error.toString());
		}
		return resultMap;
	}
    
	
	@RequestMapping(value = "download")
	public void downloadExcelModel(HttpServletResponse response,String batchId, String type){
		try {
			  CustomerErrorDto customerErrorDto=new CustomerErrorDto();
			  customerErrorDto.setBatchId(batchId);
			  ResponseList<CustomerError> list=customerErrorServiceFacade.findByDto(customerErrorDto);
			  if(list.isSuccess() && null!=list){
				  List<CustomerError> ceList=list.getEntity();
				  List<CustomerModel> cmList=new ArrayList<CustomerModel>();
				  for(CustomerError ce : ceList){
					  String rowData=ce.getRowData();
					  CustomerModel cm=JSON.parseObject(rowData, CustomerModel.class);
					  cmList.add(cm);
				  }
				  String sheetName = "客户批量导入";
				  if("subscribe".equals(type)) {
					  sheetName = "认购";
				  }
				  HSSFWorkbook wb=ExcelUtils.createExcel(cmList, sheetName);
			      String mimetype = "application/x-msdownload";
			      String downFileName = "excel-"+DateUtils.DateToString(new Date(), DateStyle.YYYY_MM_DD_HH_MM_SS)+".xls";
			      String inlineType = "attachment"; 
			      response.setHeader("Content-Disposition", inlineType  + ";filename=\"" + downFileName + "\"");
			      OutputStream out=response.getOutputStream();
			      response.setContentType(mimetype);
			      wb.write(out);
			  }
			  
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	

}
